SQL Partition By查询PIVOT行转列后日期列错位修正问题

问题原因 #

你原代码中使用按时间排序生成的行号rn作为pivot的匹配字段，第一条记录（行号=1）对应基础查询中LevelId=2的日期，因此出现值错位，L1A被错误赋值。

解决方案 #

直接用LevelId作为pivot的匹配字段即可自动对齐等级列，没有LevelId=1的记录时L1A自然为空，修改后的代码如下：

with cte as (
select projectNum, 
  [1] as L1A,
  [2] as L2A,
  [3] as L3A,
  [4] as L4A,   
  [5] as L5A
from (
  select d.projectNum, d.createdDate, d.dateId
  from (
    select dd.LevelId as dateId, dd.createdDate, dd.projectNum
    from (
      select ProjectNum, format(CreatedDate,'MM/dd/yyy') as 'CreatedDate', LevelId
      from DWCorp.SSMaster m 
      INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id 
      WHERE ActionId = 7 and projectnum = 'obel00017' and LevelId in (1,2,3,4,5)
    ) dd
    ) d
  ) as src
  pivot (
    max(createdDate)
    for dateId in ([1],[2],[3],[4],[5])
  ) as pvt)
select * from cte

修改后LevelId=2的日期会自动匹配到L2A，LevelId=3匹配到L3A，以此类推，完全符合你要的整体右移一位的预期效果。

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