SQL Pivot to Generate Dynamic Columns-CodePudding

I have the following data:

I have the following code:


select * from
(
SELECT 
d.CreatedDate,
m.siteid,
m.ProjectNum



FROM DWCorp.SSMaster m 
INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE  ActionId = 7
)as Sourcetable
pivot
(
max(createddate)
for siteid in ([1],[2],[3],[4],[5])
) As pivottable

I would like the data to look like this:

There will only ever be a maximum of 5 dates. The way I have it at the moment it pivots it by site which I don't want. I want it pivoted by date.

Can anyone help? I know that I may need to use dynamic SQL, but not sure how to do that. I have searched through the forum, but not getting exactly what I am looking for.

Text Output:

CreatedDate               siteid ProjectNum
2021-04-06 13:14:01.8933333 20  OTHO00006
2021-04-28 16:40:01.9066667 20  OTHO00006
2021-05-03 22:47:01.7466667 20  OTHO00006
2021-04-28 16:42:02.3700000 20  OTHO00016
2021-05-06 13:27:01.9633333 20  OTHO00016
2021-05-27 15:10:01.7066667 20  OTHO00018
2021-06-29 13:01:01.9266667 20  OTHO00024
2021-05-12 13:38:01.8300000 20  OTHO00024
2021-06-29 13:02:04.7800000 20  OTHO00028
2021-03-25 13:00:03.6100000 21  OBEL00001
2021-08-10 19:44:01.9233333 21  OBEL00003
2021-11-03 20:45:39.2733333 21  OBEL00003
2021-04-26 18:57:34.5533333 21  OBEL00004

CodePudding user response：

You can do this with the benefit of row_number window function and a conditional case aggregation:

with c as (
    select Convert(date,CreatedDate) CreatedDate, ProjectNum,
      Row_Number() over (partition by ProjectNum order by createddate) col
    from t
)
select 
    ProjectNum,
    max(case when col=1 then CreatedDate end) Date1,
    max(case when col=2 then CreatedDate end) Date2,
    max(case when col=3 then CreatedDate end) Date3,
    max(case when col=4 then CreatedDate end) Date4,
    max(case when col=5 then CreatedDate end) Date5
from c
group by ProjectNum

CodePudding user response：

Something like this could do the trick:

select projectNum, 
  [1] as Date1,
  [2] as Date2,
  [3] as Date3,
  [4] as Date4,
  [5] as Date5
from (
  select d.projectNum, d.createdDate, d.dateId
  from (
    select dd.rn as dateId, dd.createdDate, dd.projectNum
    from (
      select *, row_number() over (partition by projectNum order by createdDate asc) rn
      from your_data
      ) dd
    where rn <= 5
    -- order by 3, 1
    ) d
  ) as src
  pivot (
    max(createdDate)
    for dateId in ([1],[2],[3],[4],[5])
  ) as pvt

To make it easier and avoid using dynamic sql, you can use row_number in order to assign a dateId for each date ordered from oldest to newest, and then use this id to pivot the data.

CodePudding user response：

Try this but i am hoping this is only a sample usecase as for dynamic usecase there could 365 days/dates

   With dtes as (
  Select distinct trim(createdDate), row_number() over (order by 
   1) rn 
  From  DWCorp.SSMaster m 
  INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE  
  ActionId = 7 )

    SELECT 
    m.ProjectNum, 
  
     Max(case when trim(createddate) = Select 
    trim(createdDate) from dtes where rn=1 then m.siteid end), 
    Max(case when trim(createddate) = Select 
    trim(createdDate) from dtes where rn=2 then m.siteid end)
     Max(case when trim(createddate)= Select 
    trim(createdDate) from dtes where rn=3 then m.siteid end),
    Max(case when trim(createddate)= Select 
    trim(createdDate) from dtes where rn=4 then m.siteid end),
    
       Max(case when trim(createddate)= Select 
    trim(createdDate) from dtes where rn=5 then m.siteid end)
    
  FROM DWCorp.SSMaster m 
 INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE  
 ActionId = 7 
  group by project_num