动态列SQL数据透视表

Question

我试图在SQL中创建一个带有动态标题的枢轴表。我的标题工作得很好，但我不知道如何对行进行分组。

示例数据是

CriteriaID     KSB_Requirement        ModuleID    Module_Title
   1         Understand something       5         Principles 1
   1         Understand something       6         Principles 2 
   1         Understand something       7         Principles 3 
   2         Learn something            5         Principles 1
   2         Learn something            6         Principles 2

我得到的结果是：

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Module_Title) 
            FROM Standards_Coverage_Pivot_Data
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT KSB_Requirement, ' + @cols + ' from 
            Standards_Coverage_Pivot_Data 
            pivot 
            (
                Count(CriteriaID)
                for Module_Title in (' + @cols + ')
            ) p '
 execute(@query);

是..。

KSB_Requirement       Principle 1    Principle 2    Principle 3
Understand something      1              0              0
Understand something      0              1              0
Understand something      0              0              1
Learn something           1              0              0
Learn something           0              1              0

我真正想展示的是：

KSB_Requirement       Principle 1    Principle 2    Principle 3
Understand something      1              1              1
Learn something           1              1              0

任何帮助都将不胜感激。

Answer 1

你差点就到了

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Module_Title) 
            FROM Standards_Coverage_Pivot_Data
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT * from (Select KSB_Requirement,Module_Title,CriteriaID From Standards_Coverage_Pivot_Data) s
            pivot 
            (
                Count(CriteriaID)
                for Module_Title in (' + @cols + ')
            ) p '
 execute(@query);

返回

KSB_Requirement       Principles 1  Principles 2    Principles 3
Learn something       1             1               0
Understand something  1             1               1