How to Condense SQL Rows Using Stuff?

user_18191846 0
Viewed 33

I have a table of data that looks like:

#test

RecordID Name hasSpanishVersion Type TypeID
1 Test One Yes FormType1 1
1 Test One Yes FormType2 2
3 Test Three No null null
4 Test Four Yes FormType3 3
5 Test Five Yes FormType3 3

I also have another table that looks like:

#formTypes

TypeID FormType
1 FormType1
2 FormType2
3 FormType3

What I am trying to do is condense the Type column where there are like-RecordIDs / Names. If "hasSpanishVersion" is null, the following two columns will also be null.

I am wanting the example table to look like:

RecordID Name hasSpanishVersion Type
1 Test One Yes FormType1, FormType2
3 Test Three null null
4 Test Four Yes FormType3
5 Test Five Yes FormType3

I have tried the following code, but this only takes all of the FormTypes and condenses them for each of the three different types:

SELECT 
   *,
   STUFF((SELECT '; ' + t.formTypeSpanish 
          FROM #test t
          WHERE t.TypeID = ft.TypeID
          FOR XML PATH('')), 1, 1, '') as FormTypes
FROM #formTypes ft
GROUP BY ft.TypeID, ft.FormType
ORDER BY 1
sql-server sql
1 Answers

You might let your grouping column put in Where in a correlated subquery that and you concatenate value in SELECT

SELECT 
   RecordID, Name,hasSpanishVersion,
   STUFF((SELECT ',' + tt.[Type] 
          FROM formTypes tt
          WHERE 
          tt.RecordID = t1.RecordID AND 
          tt.Name = t1.Name AND
          tt.hasSpanishVersion = t1.hasSpanishVersion
          FOR XML PATH('')), 1, 1, '') as FormTypes
FROM formTypes t1
GROUP BY RecordID, Name,hasSpanishVersion
ORDER BY 1

if your sql-server support STRING_AGG there is another simple way to do that.

SELECT RecordID, Name,hasSpanishVersion,STRING_AGG([Type] ,',') 
FROM formTypes
GROUP BY RecordID, Name,hasSpanishVersion

sqlfiddle

user_51760710
Related