January 20, 2012 at 2:17 pm
Hi all - I've been tasked with de-duplicating some customer data and am having difficultly getting the query right, I'm hoping someone can help me out. I have a table that looks something like this:
DECLARE @data TABLE (CustomerID INT IDENTITY(1,1), LastName NVARCHAR(100), FirstName NVARCHAR(100))
INSERT INTO @data (LastName,FirstName)
SELECT 'Smith','John'
UNION ALL
SELECT 'Smith','John'
UNION ALL
SELECT 'Ford','Sam'
UNION ALL
SELECT 'Knox','Katie'
UNION ALL
SELECT 'Knox','Katie'
The output I'm trying to achieve would look like this where "CustomerString" would be the concatenation of the CustomerID values:
LastNameFirstNameRecordCountMinCustomerIDCustomerString
FordSam1 33
KnoxKatie244,5
SmithJohn211,2
I can get the first four columns easily enough and have been trying variations of STUFF FOR XML PATH but the GROUP BY CustomerID requirement is expanding the result set.
SELECT T1.LastName,T1.FirstName,
COUNT(T1.CustomerID) AS RecordCount,MIN(T1.CustomerID) AS MinCustomerID,
STUFF((SELECT DISTINCT ', ' + CONVERT(NVARCHAR,T2.CustomerID) FROM @data T2 WHERE T1.CustomerID = T2.CustomerID FOR XML PATH('')),1,2,'') AS CustomerString
FROM @data T1
GROUP BY T1.CustomerID,T1.LastName,T1.FirstName
ORDER BY T1.LastName,T1.FirstName
Any help would be greatly appreciated!
January 20, 2012 at 2:21 pm
Try this article for some assistance
http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2012 at 2:26 pm
Coupla things. You needed to stop grouping by the customer ID in the Group BY and you needed to have the REPLACE/STUFF component work off FirstName/LastName combination instead of the CustomerID so it'd group properly.
Try this:
SELECT T1.LastName, T1.FirstName,
COUNT(T1.CustomerID) AS RecordCount,
MIN(T1.CustomerID) AS MinCustomerID,
REPLACE((SELECT DISTINCT CONVERT(NVARCHAR,T2.CustomerID) + ','
FROM @Data T2
WHERE T1.FirstName = T2.FirstName
AND t1.LastName = T2.LastName
FOR XML PATH(''))+ '$' , ',$', '') AS CustomerString
FROM @Data T1
GROUP BY T1.LastName,T1.FirstName
ORDER BY T1.LastName,T1.FirstName
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 20, 2012 at 2:31 pm
Perfect, thanks so much for the quick response!
January 20, 2012 at 2:38 pm
TheRealDJ (1/20/2012)
Perfect, thanks so much for the quick response!
My pleasure, thanks for setting up sample data and structure and showing your work so we could help you quickly and efficiently. 🙂 See ya next time!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 22, 2012 at 8:01 pm
An alternative, that I find easier to read (and write!):
DECLARE @Data TABLE
(
CustomerID integer IDENTITY(1,1) PRIMARY KEY,
LastName nvarchar(100) NOT NULL,
FirstName nvarchar(100) NOT NULL
);
INSERT @Data
(LastName, FirstName)
VALUES
('Smith', 'John'),
('Smith', 'John'),
('Ford', 'Sam'),
('Knox', 'Katie'),
('Knox', 'Katie');
SELECT
d.LastName,
d.FirstName,
RecordCount = COUNT_BIG(*),
MinCustomerID = MIN(d.CustomerID),
CustomerString = dbo.Concatenate(d.CustomerID)
FROM @Data AS d
GROUP BY
d.LastName, d.FirstName
ORDER BY
d.LastName, d.FirstName;
Uses:
CREATE ASSEMBLY [Concatenation]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030053CC1C4F0000000000000000E00002210B010800000E000000060000000000005E2C00000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000000C2C00004F00000000400000A003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000640C000000200000000E000000020000000000000000000000000000200000602E72737263000000A0030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000402C0000000000004800000002000500C02100004C0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003202731100000A7D010000042A720F01281200000A2D12027B010000040F01281300000A6F1400000A2A4E027B010000040F017B010000046F1500000A2A1E02281600000A2A56027B020000041F2C6F1700000A036F1800000A262A133004006900000001000011027B010000046F1900000A2D0A1201FE1505000001072A73070000060A027B010000046F1A00000A06731B00000A7D02000004027B0100000406FE0608000006731C00000A6F1D00000A067B0200000417067B020000046F1E00000A17596F1F00000A732000000A2A1E02281600000A2A36027B03000004036F2100000A2A00133004003B0000000200001173090000060A06037D03000004067B03000004027B010000046F1900000A6F2100000A027B0100000406FE060A000006731C00000A6F1D00000A2A00133003003100000003000011036F2200000A0A0206732300000A7D01000004160B2B15027B01000004036F2200000A6F1400000A0717580B070632E72A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000088030000237E0000F4030000A804000023537472696E6773000000009C0800000800000023555300A4080000100000002347554944000000B40800009801000023426C6F6200000000000000020000015717020A0902000000FA253300160000010000001C00000004000000030000000A0000000600000001000000240000000D00000003000000020000000200000001000000020000000200000000000A000100000000000600380031000A0069004E00060095007A000A00BF00AA000A00D900AA000600F700ED0006003C01ED000600A1018F010600BE018F010600DB018F010600FA018F01060013028F0106002C028F01060047028F01060062028F0106009A027B020600AE028F010600E702C70206000703C7020A0033034E000A0054034E0006005B037B02060071037B02060095037A000600BF0331000600D203C60306001104310006008B04C7020000000001000000000001000100090110001C00000005000100010003011000AC030000650002000700030110003604000065000300090001009C000A000600E003DE0006008B0111015020000000008600A500110001005D20000000008600C800150001007A20000000008600D3001B000200AC20000000008600E30021000300382100000000E101040126000300802100000000E10149012C0004008E20000000008618B801110005009620000000008600E3033C0005002121000000008618B80111000600292100000000860049043C000600000001007F01000001008501000001008B01000001008D0100000100F30300000100F3030200090011003601260011007A012C004100B80132004900B80132005100B80132005900B80132006100B80132006900B80132007100B80132007900B80132008100B80137008900B80132009100B8013C009900B8011100A100B8014100B100B801BA000C00B801110021007C03C60021008703CA000C009103CE000C00A303D400C900B8011100D100FB03E200D100FB03E8000C000204CA000C000C041100D100B80111001400B801F4000C001A04FA00D1002204CA00D1002D0404012900B8013200310036013C0039008104CA000C00B8013C00E100B80111002E0033003D012E00730076012E001B0024012E00230037012E002B0037012E003B0024012E0043004C012E004B0037012E005B0037012E006B006D0143007B004700630023011F01830023011F010A0115011A0102000A00030002000C000500C000EE00048000000100000035111170000000000000250300000200000000000000000000000100280000000000020000000000000000000000010042000000000003000200040002000000003C4D6F64756C653E00436F6E636174656E6174696F6E2E646C6C00436F6E636174656E617465006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C697374603100636F6E74656E747300496E69740053797374656D2E446174612E53716C54797065730053716C496E74333200416363756D756C617465004D657267650053716C537472696E67005465726D696E6174650053797374656D2E494F0042696E617279577269746572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E57726974650057726974650042696E617279526561646572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E5265616400526561640056616C75650047726F7570007700720053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500436F6E636174656E6174696F6E0053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E64006765745F49734E756C6C006765745F56616C7565004164640049456E756D657261626C6560310041646452616E6765003C3E635F5F446973706C6179436C61737331004F626A6563740053797374656D2E5465787400537472696E674275696C646572007362003C5465726D696E6174653E625F5F3000656C656D656E7400417070656E64006765745F436F756E7400536F727400416374696F6E603100466F7245616368006765745F4C656E67746800546F537472696E67003C3E635F5F446973706C6179436C61737334003C4D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E57726974653E625F5F330052656164496E74333200436F6D70696C657247656E6572617465644174747269627574650000000003200000000000FEBDF527DCB9E54D8CDB77126297226E0008B77A5C561934E089060615120D010803200001052001011111052001011108042000111505200101121905200101121D042001010E042001010204200101080520010111557201000200000005005402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4E756C6C73015402124973496E76617269616E74546F4F726465720154020D49734E756C6C4966456D7074790154080B4D61784279746553697A65FFFFFFFF05200101115D0515120D0108032000020320000805200101130009200101151261011300030612690520011269030520011269080515126D0108052002011C180920010115126D0113000520020E0808060702120C1115030612190407011210040702080804010000001201000D436F6E636174656E6174696F6E00000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000342C000000000000000000004E2C0000002000000000000000000000000000000000000000000000402C0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000480300000000000000000000480334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001001170351100000100117035113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A8020000010053007400720069006E006700460069006C00650049006E0066006F00000084020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043006F006E0063006100740065006E006100740069006F006E00000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003400300035002E00320038003600380039000000000044001200010049006E007400650072006E0061006C004E0061006D006500000043006F006E0063006100740065006E006100740069006F006E002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043006F006E0063006100740065006E006100740069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D0065000000000043006F006E0063006100740065006E006100740069006F006E00000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003400300035002E00320038003600380039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003400300035002E003200380036003800390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000603C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
CREATE AGGREGATE [dbo].[Concatenate] (@Value [int])
RETURNS [nvarchar](4000)
EXTERNAL NAME [Concatenation].[Concatenate];
Source:
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Server;
[SqlUserDefinedAggregate
(
Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = true,
MaxByteSize = -1
)
]
public struct Concatenate : IBinarySerialize
{
private List<int> contents;
public void Init()
{
contents = new List<int>();
}
public void Accumulate(SqlInt32 Value)
{
if (!Value.IsNull)
{
contents.Add(Value.Value);
}
}
public void Merge(Concatenate Group)
{
contents.AddRange(Group.contents);
}
public SqlString Terminate()
{
if (contents.Count == 0)
{
return new SqlString();
}
else
{
contents.Sort();
var sb = new StringBuilder();
contents.ForEach(element => sb.Append(',').Append(element));
return new SqlString(sb.ToString(1, sb.Length - 1));
}
}
void IBinarySerialize.Write(BinaryWriter w)
{
w.Write(contents.Count);
contents.ForEach(element => w.Write(element));
}
void IBinarySerialize.Read(BinaryReader r)
{
int count = r.ReadInt32();
contents = new List<int>(count);
for (int i = 0; i < count; i++)
{
contents.Add(r.ReadInt32());
}
}
}
April 20, 2012 at 1:32 pm
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply