pivoting on a column without knowing the entire list of values
I have a table
Title Name Type
------------------------------------------------
T1 A Primary
T1 B Primary
T2 B Primary
T2 开发者_JAVA百科 C Secondary
T2 D Secondary
I need the output to be
Title Primary Secondary
------------------------------------------------
T1 A, B NULL/Blank
T2 B C, D
[Name] column in the original table can have any value. i.e. later there could be E, F, G etc.
How can this be done?
Then you need dynamic SQL. Consider something like this for generating the list of columns:
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 construct the query you want, which you then run 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;
精彩评论