SQL Group By with Text Transformation

Question

I'm trying to do some transformations on a large data set that I'm working on and was hoping for a bit of assistance on a particular grouping. I have a series of records that follow a pattern similar to below:

Language   Full Name     Customer ID   
--------------------------------------               
English    John Smith    12222
French     John Smith    12222
Spanish    John Smith    12222
English    Karen Wong    55999
Cantonese  Karen Wong    55999

I need the data such that the Full Name and Customer ID are not repeated so simply using DISTINCT for that. However, one oddity in the requirement is that all the different languages need to be preserved and squashed into the resulting output so the resulting data needs to look like this:

Languages Spoken           Full Name     Customer ID   
----------------------------------------------------            
English, French, Spanish   John Smith    12222
English, Cantonese         Karen Wong    55999

Sounded like a simple thing but I guess I'm not a big SQL guru and keep getting funny results. Any help would be much appreciated :)

Google: "SQL Server string aggregation". – Gordon Linoff Jun 18 '18 at 22:01 — Gordon Linoff, Jun 18 '18 at 22:01

score 3 · Answer 1 · answered Jun 18 '18 at 22:06

3

If you're using SQL Server 2017 or Azure SQL than you can just use STRING_AGG

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

For everything else (covers solutions from SQL Server 2005 and on):

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

answered Jun 18 '18 at 22:06

mauridb

1,467
9
12

I learned something new... thanks! This is what I was looking for. – Nixman55 Jun 18 '18 at 22:12

SQL Group By with Text Transformation

1 Answers1