Tabla dinámica de SQL de columna dinámica

Estoy tratando de crear una tabla dinámica en SQL con títulos dynamics. Tengo los títulos que funcionan bien, pero no sé cómo agrupar las filas.

Ejemplo de datos es

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 

El resultado que obtengo usando:

 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); 

es….

 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 

Lo que realmente quiero mostrar es:

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

Cualquier ayuda sería muy apreciada.

Estabas casi allí

 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); 

Devoluciones

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