Move date column across in SQL Partition By Clause

December 2, 2021

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.

>Solution :

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