Move date column across in SQL Partition By Clause-CodePudding

I have the following code:


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.rn as dateId, dd.createdDate, dd.projectNum
    from (
      select ProjectNum, format(CreatedDate,'MM/dd/yyy') as 'CreatedDate', row_number() over (partition by projectNum order by createdDate asc) rn
      from DWCorp.SSMaster m 
INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE  ActionId = 7 and projectnum = 'obel00017'
      ) dd
    where rn <= 5
   -- order by 3, 1
    ) d
  ) as src
  pivot (
    max(createdDate)
    for dateId in ([1],[2],[3],[4],[5])
    
  ) as pvt)

  
  
  select *  from cte

Which returns:

When I run this query, which the above query is based on:

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)

It returns:

I need the result to be in the correct columns. L1A should not have a value in it, and everything should shift one to the right. Not sure why this is happening. Example of how it should look below.

CodePudding user response：

The pivot query is calculating a row_number for the column.

But you already got that LevelId.

So replace it.

select 
  projectNum
, [1] as L1A
, [2] as L2A
, [3] as L3A
, [4] as L4A
, [5] as L5A
from
(
  select 
    ProjectNum
  , format(CreatedDate,'MM/dd/yyyy') as CreatedDate
  , LevelId
  from DWCorp.SSMaster m 
  join DWCorp.SSDetail d on d.MasterId = m.Id 
  where  ActionId = 7 
  and projectnum = 'obel00017' 
  and LevelId <= 5
) as src
pivot 
(
  max(createdDate)
  for LevelId in ([1],[2],[3],[4],[5])
) as pvt