In SQL Server: Create Pivot Table with each Date as a own column

Question

I have a table containing 4 different identifiers, a date and a numeric column like:

ID1 | ID2 | ID3 | ID4 | my_date | in_stock
A | A | A | C | 06.06.2020 | 2880
A | A | A | C | 05.06.2020 | 2880
A | A | A | D | 06.06.2020 | 5000
A | A | A | D | 05.06.2020 | 6000

from which I want to create a kind of pivot table like the following:

ID1 | ID2 | ID3 | ID4 | 05.06.2020| 06.06.2020
A | A | A | C | 2880 | 2880
A | A | A | D | 6000 | 5000

This should be created for past 10 days or so, so it CRUCIAL I don't have to set the dates manually. I tried something with PIVOT but that didn't work out so I switched to do the job with CASE WHEN like the following:

SELECT ID, ID2, ID3, ID4, 
    SUM(CASE WHEN my_date = DATEADD(DAY, -0, (SELECT MAX(my_date) FROM my_table)) then in_stock END) DATEADD(DAY, 0, (SELECT MAX(my_date) FROM my_table)),
    SUM(CASE WHEN my_date = DATEADD(DAY, -1, (SELECT MAX(my_date) FROM my_table)) then in_stock END) DATEADD(DAY, -1, (SELECT MAX(my_date) FROM my_table))
FROM
(
    SELECT ID, ID2, my_date, ID3, in_stock, ID4
    FROM RIAMDB.dbo.my_table my_table
    WHERE (my_date>DATEADD(DAY, -2, (SELECT MAX(my_date) FROM my_table)))
) src
GROUP BY ID, ID2, ID3, ID4

but that still doesn't work out as I get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DAY'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'src'.

How would you guys approach this issue is CASE the right way to do it or rather use PIVOT somehow? Or is there any s

John Cappelletti · Accepted Answer · 2020-06-24 16:31:39Z

2

Seems like you are looking for a Dynamic Pivot

Example

Declare @SQL varchar(max) = '
Select *
 Into  ANewTable
 From  (
         Select ID1
               ,ID2
               ,ID3
               ,ID4
               ,My_Date
               ,In_Stock
          From  YourTable
          Where my_date>= dateadd(DAY,-10,convert(date,getdate()))
        ) src
 Pivot (sum(In_stock) For [my_date] in (' + Stuff((Select Distinct concat(',[',my_date,']') 
                                                    From  YourTable 
                                                    Where my_date>= dateadd(DAY,-10,convert(date,getdate()))
                                                    Order By 1 
                                                    For XML Path('')),1,1,'')  + ') ) p
'
--Print @SQL
Exec(@SQL);

Select * from ANewTable

Returns

ID1 ID2 ID3 ID4 2020-06-05  2020-06-06
A   A   A   C   2880        2880
A   A   A   D   6000        5000

EDIT - Last Update - Next time ask a new question

Declare @SQL varchar(max) = '
Select *
 Into  ANewTable
 From  (
         Select ID1
               ,ID2
               ,ID3
               ,ID4
               ,Col = concat(''t-'',datediff(day,my_date,getdate()))
               ,In_Stock
          From  YourTable
          Where my_date>= dateadd(DAY,-10,convert(date,getdate()))
        ) src
 Pivot (sum(In_stock) For [col] in (' + Stuff((Select concat(',[',col,']') 
                                                    From  ( Select top 10 
                                                                   my_date
                                                                  ,col=concat('t-',datediff(day,my_date,getdate()))
                                                             From  YourTable
                                                             Where my_date>= dateadd(DAY,-10,convert(date,getdate()))
                                                             Group By my_date 
                                                             Order By my_date desc
                                                          ) A
                                                     For XML Path('')),1,1,'')  + ') ) p
'
--Print @SQL
Exec(@SQL);

Select * from ANewTable

Returns

edited Jun 24, 2020 at 16:31

answered Jun 6, 2020 at 18:41

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

John Cappelletti Over a year ago

@Quastiat See my updated answer. Rather than From YourTable, we simply added a subquery detailing the desired columns and included a WHERE my_date ...

John Cappelletti Over a year ago

@Quastiat Just rember, you need to "Feed" PIVOTS with the desired columns.

John Cappelletti Over a year ago

@Quastiat Always happy to help. Uncomment the Print SQL. This way you can see the SQL which is executed.

John Cappelletti Over a year ago

@Quastiat change Order By 1 to Order By 1 Desc

John Cappelletti Over a year ago

@Quastiat Just an afterthought, in the two places you see datediff(day,my_date,getdate()) depending on the desired order you may want to flip the parameters of my_date and getdate() or even change Order By my_date desc to Order By my_date asc

|

Collectives™ on Stack Overflow

In SQL Server: Create Pivot Table with each Date as a own column

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related