SQL 如何以日期为列动态旋转
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27422109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pivot dynamically with date as column
提问by Craphex
I have a table with product id's and names, and another table with the stock of these products on certain dates. Such as Item1had 6stock on 1-1-2014and 8stock 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 和名称的表格，以及另一个包含特定日期这些产品库存的表格。比如Item1had 6stock on1-1-2014和8stock on 2-1-2014。我试图在存储过程中显示这些内容，使其看起来像一个日历，显示一个月中的所有日期以及单元格中的可用库存。展示这一点的最佳方式是什么？
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
原始表 - 库存日期
ID | NameID | Stock | Date
1 | 1 | 8 | 2-1-2014
2 | 2 | 2 | 4-1-2014
回答by Sarath Avanavu
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
将连接数据放入临时表
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
获取数据透视列
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
你的结果在这里
回答by B0Andrew
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:
加入dates（从上面）与products（完整）和与stock（左）这样你得到一个这样的表：
date
product_id
items
For products and dates where stock is not available you display 0. isnullwill do the trick. Make sure the datecolumn is converted to varcharin the same format as in CTE above.
对于没有库存的产品和日期，您显示 0. 即可isnull。确保将date列转换为varchar与上述 CTE 中相同的格式。
Step 3 - pivot the table (obtained at step 2) by datecolumn in a dynamic query.
步骤 3 -date在动态查询中按列对表（在步骤 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
我可以给你更多细节，但不是现在。您可以在另一个响应中看到类似的内容： Spread distinct values over different columns
