如何使用SQL PIVOT将日期显示为行 解决查询返回NULL问题

问题根因 #

PIVOT 运算默认会将源查询中除聚合函数用到的列、FOR子句指定的待转列之外的所有列作为分组依据。你当前的源查询仅包含三个列：siteid（待转列）、CreatedDate（聚合列）、ProjectNum，而WHERE条件已经固定了ProjectNum为唯一值，因此分组键只有一个固定值，所有聚合结果都会挤在同一行，没有匹配数据的siteid列就会返回NULL。

调整方案 #

如果需要将不同的日期拆分到对应行展示，只需在源查询中增加行号作为额外的分组键即可，调整后代码如下：

select * from
(
SELECT 
m.siteid,
d.CreatedDate,
m.ProjectNum,
-- 新增行号作为分组键，同siteid下按日期排序编号
ROW_NUMBER() OVER(PARTITION BY m.siteid ORDER BY d.CreatedDate) as rn
FROM DWCorp.SSMaster m 
INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE m.ProjectNum = 'OJER00025' and d.ActionId = 7
)as Sourcetable
pivot
(
max(createddate)
for siteid in ([1],[2],[3],[4],[5])
) As pivottable 

如果不需要在结果中展示行号，只需将外层的select *改为指定需要返回的列即可：

select ProjectNum, [1],[2],[3],[4],[5] from
(
SELECT 
m.siteid,
d.CreatedDate,
m.ProjectNum,
ROW_NUMBER() OVER(PARTITION BY m.siteid ORDER BY d.CreatedDate) as rn
FROM DWCorp.SSMaster m 
INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE m.ProjectNum = 'OJER00025' and d.ActionId = 7
)as Sourcetable
pivot
(
max(createddate)
for siteid in ([1],[2],[3],[4],[5])
) As pivottable 

内容的提问来源于stack exchange，提问作者Jeremy Reynolds