How to Condense SQL Rows Using Stuff?-CodePudding

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

CodePudding user response：

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