Rotate a column without knowing the complete list of values

Question

I have a table

Title Name Type ------------------------------------------------ T1 A Primary T1 B Primary T2 B Primary T2 C Secondary T2 D Secondary 

I need a conclusion

 Title Primary Secondary ------------------------------------------------ T1 A, B NULL/Blank T2 BC, D 

Column

[Name] in the source table can have any value. that is, later there may be E, F, G, etc.

How can I do that?

Answer 1

Then you need dynamic SQL. To create a list of columns, consider something like this:

 DECLARE @collist nvarchar(max); SELECT @collist = STUFF((SELECT ', ' + quotename(Type) FROM YourTable GROUP BY Type FOR XML PATH('')), 1, 2, ''); 

Now you can use @collist to help build the query you want, which is then triggered using sp_executesql

Like this:

 DECLARE @collist nvarchar(max); SELECT @collist = STUFF((SELECT ', ' + quotename(Type) FROM YourTable GROUP BY Type FOR XML PATH('')), 1, 2, ''); DECLARE @qry nvarchar(max); SET @qry = N' SELECT Title, ' + @collist + ' FROM ( SELECT t.Title, t.Type, (SELECT STUFF((SELECT '', '' + t2.Name FROM YourTable t2 WHERE t2.Title = t.Title AND t2.Type = t.Type ORDER BY t2.Name FOR XML PATH('''')),1,2,'''')) AS Names FROM YourTable t GROUP BY t.Type, t.Title ) tg pivot (max(Names) for tg.Type in (' + @collist + ')) p '; exec sp_executesql @qry;