如何以日期为列动态旋转 [英] How to pivot dynamically with date as column

问题描述

我有一个带有产品ID和名称的表格，以及一个带有某些日期这些产品的库存的表格.例如Item1在1-1-2014上具有6库存，在2-1-2014上具有8库存. 我试图在存储过程中显示这些内容，使其看起来像日历，显示一个月中的所有日期以及单元格中的可用库存.最好的显示方式是什么?

I have a table with product id's and names, and another table with the stock of these products on certain dates. Such as Item1 had 6 stock on 1-1-2014 and 8 stock on 2-1-2014. I'm trying to show these in a stored procedure so that it looks like a calendar, showing all the dates in a month and the stock available in the cells. What is the best way to show this?

例如:

Name  | 1-1-2014 | 2-1-2014 | 3-1-2014 | 4-1-2014
Item1 |     6    |     8    |          |    6
Item2 |          |     2    |     1    |

原始表格-名称

 ID |   Name 
  1 |  Item1
  2 |  Item2

原始表格-库存日期

 ID | NameID  | Stock |    Date 
  1 |    1    |   8   |  2-1-2014    
  2 |    2    |   2   |  4-1-2014 

推荐答案

这是您的示例表

SELECT * INTO #Names
FROM
(
SELECT 1 ID,'ITEM1' NAME 
UNION ALL
SELECT 2 ID,'ITEM2' NAME 
)TAB

SELECT * INTO #Stockdates
FROM
(      
SELECT 1 ID,1 NAMEID,8 STOCK,'2-1-2014 ' [DATE]
UNION ALL
SELECT 2 ID,2 NAMEID,2 STOCK,'4-1-2014 ' [DATE]
)TAB

将联接数据放入温度表

SELECT N.NAME,S.[DATE],S.STOCK 
INTO #TABLE
FROM #NAMES N
JOIN #Stockdates S ON N.ID=S.NAMEID

获取用于数据透视的列

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', 
               '[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
               FROM    (SELECT DISTINCT [DATE] FROM #TABLE) PV  
               ORDER BY [DATE]

现在旋转

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT * FROM 
             (
                 SELECT * FROM #TABLE
             ) x
             PIVOT 
             (
                 SUM(STOCK)
                 FOR [DATE] IN (' + @cols + ')
            ) p      

            '     
EXEC SP_EXECUTESQL @query

您的结果在这里

这篇关于如何以日期为列动态旋转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！