Question

页: 1

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

我需要产出。

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

原始表格中的[Name]栏可具有任何价值。 i. 嗣后可有E、F、G等。

如何做到这一点?

Answer 1

然后,你们需要充满活力的食堂。 2. 考虑编制栏目清单的类似内容:

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

现在,你可以使用@collist来帮助构建你想要的询问,然后使用<代码>sp_executesql。

与此类似:

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;

友情链接