如何使用日期作为列动态旋转

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?

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

For example:

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

Original tables - Names

原始表 - 名称

 ID |   Name 
  1 |  Item1
  2 |  Item2

Original tables - Stockdates

原始表 - Stockdates

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

2 个解决方案

#1

Here is your sample table

这是你的样本表

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

Put the join data to a temperory table

将连接数据放入一个temperory表

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

Get the columns for pivot

获取pivot的列

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]

Now pivot it

现在转动它

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

            '     
EXEC SP_EXECUTESQL @query

And your result is here

你的结果就在这里

#2

Step 1 - Fill the gaps (maybe not required)

第1步 - 填补空白(可能不需要)

If your stocks table does not contain stock from every day for every product then you have to get all the dates in a month from somewhere else. You can generate them with a recursive CTE: (variable declarations are omitted)

如果您的股票表每天都没有包含每种产品的库存,那么您必须从一个其他地方获得一个月内的所有日期。您可以使用递归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;

You can use your preferred date format but it's important to maintain it in all queries.

您可以使用首选日期格式,但在所有查询中保留它是很重要的。

Step 2 - Bring data to a normal form. You can chose to store it in a temporary table or you can use a CTE again and combine this step with step 3.

第2步 - 将数据转换为正常形式。您可以选择将其存储在临时表中,也可以再次使用CTE并将此步骤与步骤3结合使用。

Join dates (from above) with products (full) and with stock (left) so you obtain a table like this:

加入日期(从上面)与产品(完整)和股票(左),所以你得到这样的表:

date
product_id    
items

For products and dates where stock is not available you display 0. isnull will do the trick. Make sure the date column is converted to varchar in the same format as in CTE above.

对于库存不可用的产品和日期,您显示0. isnull将起作用。确保将日期列转换为varchar,格式与上面的CTE相同。

Step 3 - pivot the table (obtained at step 2) by date column in a dynamic query.

步骤3 - 在动态查询中按日期列旋转表(在步骤2中获得)。

I can give you more details but not right now. You can see something similar in another response: Spread distinct values over different columns

我可以给你更多细节但不是现在。您可以在另一个响应中看到类似的内容:在不同的列上传播不同的值