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

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

Answer

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