sqlserver 行列转换(行传换为列)

1.原始查询

SELECT s.Name AS ShiftName,
        h.BusinessEntityID,
        d.Name AS DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s
ON h.ShiftID = s.ShiftID
WHERE EndDate IS NULL
AND d.Name IN ('Production', 'Engineering', 'Marketing')
ORDER BY ShiftName;

结果如下

在这个结果集中，我们可以看到所有的部门都列在一个列中。下一步是将此查询返回的部门值转换为列，以及按班次计算的员工数。

想要实现的效果

2.用PIVOT关键字进行行列转换

SELECT  ShiftName,        
                Production,        
                Engineering,        
                Marketing
FROM    (SELECT s.Name AS ShiftName,       
                            h.BusinessEntityID,      
                            d.Name AS DepartmentName         
                FROM   HumanResources.EmployeeDepartmentHistory h                
                INNER JOIN HumanResources.Department d                    
                ON h.DepartmentID = d.DepartmentID                
                INNER JOIN HumanResources.Shift s                    
                ON h.ShiftID = s.ShiftID         
                WHERE  EndDate IS NULL                
                AND d.Name IN ('Production', 'Engineering', 'Marketing')        
                ) AS a
PIVOT
    ( 
    COUNT(BusinessEntityID)
    FOR DepartmentName IN ([Production], [Engineering], [Marketing])
    )  AS b
ORDER BY ShiftName;

结果如下:

3.转换的语法如下:

 4.参数的定义如下:

官网示义

 

 

 

5.在引入PIVOT操作符之前,我们是通过以下语句进行转换的

SELECT  s.Name AS ShiftName,      
                SUM(CASE WHEN d.Name = 'Production' THEN 1 ELSE 0 END) AS Production,        
                SUM(CASE WHEN d.Name = 'Engineering' THEN 1 ELSE 0 END) AS Engineering,       
                SUM(CASE WHEN d.Name = 'Marketing' THEN 1 ELSE 0 END) AS Marketing
FROM    HumanResources.EmployeeDepartmentHistory h       
INNER JOIN HumanResources.Department d            
ON h.DepartmentID = d.DepartmentID       
INNER JOIN HumanResources.Shift s         
ON h.ShiftID = s.ShiftID
WHERE   h.EndDate IS NULL       
AND d.Name IN ('Production', 'Engineering', 'Marketing')
GROUP BY s.Name;

也是同样结果

6.这两种方法无论是用PIVOT关键字还是直接聚合运算都需要事先知道部门的名称,因为必须要有列名,

这种情况可以先查询一下部门,然后动态拼接进column_list ,但是这种动态拼接似乎用第二种方式无法实现