在sql server中：创建透视表，每个日期都作为自己的列

ep6jt1vc 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(376)

我有一个包含4个不同标识符的表，一个日期和一个数字列，如：

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

我想从中创建一种透视表，如下所示：

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

这应该是过去10天左右创建，所以至关重要的是我不必手动设置日期。我试过用pivot做些什么，但没有成功，所以我改用pivot做这项工作 CASE WHEN 如下所示：

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

但我现在还是不明白

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'.

你们是如何处理这个问题的，是正确的方法，还是以某种方式使用pivot？还是有什么问题

sql sql-server pivot pivot-table

来源：https://stackoverflow.com/questions/62236234/in-sql-server-create-pivot-table-with-each-date-as-a-own-column

1条答案

按热度按时间

vcirk6k61#

似乎你在寻找一个动态的支点
例子

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

退货

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

编辑-上次更新-下次询问新问题

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

退货

赞(0）回复(0）举报 2021-07-26

我来回答

在sql server中：创建透视表，每个日期都作为自己的列

1条答案

相关问题

热门标签

最新问答