如何查询和显示数据作为一个矩阵

Question

我的查询:

SELECT *
FROM   
    (SELECT  
         t.Description,
         ISNULL(COUNT(e.EmployeeCode), 0) 
     FROM    
         Employee AS e  
     INNER JOIN 
         Department AS d ON e.DepartmentCode = d.DepartmentCode
     INNER JOIN 
         TermReason AS t ON e.TermReasonCode = t.TermReasonCode  
     WHERE   
         e.terminationdate IS NOT NULL
         AND e.TerminationDate BETWEEN @PeriodStartDate AND @PeriodEndDate) s
PIVOT   
    (COUNT(EmployeeCode) FOR description IN 
        ([Finance], [Human Resources], [Nursing])) pvt

我的预期结果是:

Termination Reason Department1 Department2 Department3 etc etc
---------------------------------------------------------------------
    aaa       value1       value2        value 3           value4
    bbb       value1       value2        value 3           value4
    ccc       value1       value2        value 3           value4
    ddd       value1       value2        value 3           value4

Answer 1

动态枢轴

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
   + QUOTENAME(Week)
    FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks
    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
      N'SELECT Store, ' + @ColumnName + '
        FROM #StoreSales
        PIVOT(SUM(xCount) 
              FOR Week IN (' + @ColumnName + ')) AS PVTTable'
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery