如何将日期作为列进行动态枢轴

Question

我有一个表，上面有产品id和名称，还有另一个表，上面有这些产品在特定日期的库存。例如Item1在1-1-2014上有6股票，在2-1-2014上有8股票。我试图在存储过程中显示这些数据，使其看起来像日历，显示一个月内的所有日期和单元格中可用的股票。什么是最好的方式来展示这一点？

例如，：

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

原始表- Stockdates

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

Answer 1

这是你的样品表

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

你的结果在这里

​

​

Answer 2

第一步-填补空白(可能不是必需的)

如果您的库存表不包含每个产品每天的库存，那么您必须在一个月内从其他地方获取所有日期。您可以使用递归CTE生成它们：(省略变量声明)

with dates as
(
   select @startdate as [date]
   union ALL
   select   [date] + 1 
   from dates
   where    [date] < @enddate
)
select  @strDays = COALESCE(@strDays + ', ['+ convert(varchar(8), [date],112) + ']', '['+ convert(varchar(8), [date],112) + ']') 
from    dates;

您可以使用您喜欢的日期格式，但是在所有查询中维护它是很重要的。

步骤2-将数据带到正常的表单中。您可以选择将其存储在临时表中，也可以再次使用CTE，并将此步骤与步骤3相结合。

将dates (从上面)与products (full)和stock (左)连接，这样您就可以获得如下表：

date
product_id    
items

对于无法使用库存的产品和日期，请显示0。isnull会做到这一点的。确保date列以与上述varchar相同的格式转换为varchar。

步骤3-通过动态查询中的date列将表(在步骤2中获得)枢轴。

我可以给你更多的细节，但现在不行。您可以在另一个响应中看到类似的内容：Spread distinct values over different columns