問題描述
我對 SQL 很陌生，正在嘗試解決這個問題:
I am pretty new to SQL and trying to figure this out:
我有一個名為 BUDGET 的表，其中包含一年中每個月的 12 列，顯示該月的預算余額.所以表格看起來像這樣:
I have a table called BUDGET that has 12 columns for each month of the year, displaying the budget balance of that month. So the table looks like this:
[Department] [Year] [Month1] [Month2] .... [Month12]
ABCD 2010 $5000 $5500 ..... $4000
ABCD 2011 $6000 $6500 ..... $3000
我想要做的是標準化這個表并將每一行分成 12 行，每行有一個日期字段，格式如下.我還想有一個 [余額] 列來顯示該月的值.因此，規范化的表將如下所示:
What I am trying to do is to normalize this table and break each row into 12 rows, each row with a date field in the following format. I also want to have a [Balance] column that displays the value of that month. So, the normalized table will look like this:
[Department] [Date] [Balance]
ABCD 20100101 $5000
ABCD 20100201 $5500
ABCD 20100301 .....
ABCD ....... ......
我嘗試在同一張桌子上使用 CROSS JOIN 但失敗了.我也嘗試使用 while 循環，但也失敗了.任何形式的幫助表示贊賞.謝謝！
I tried using CROSS JOIN on the same table but failed. I also tried using a while loop but that failed as well. Any kind of help is appreciated. Thanks!
我使用的是 SQL Server 2008
I am using SQL Server 2008
推薦答案
為了好玩，這里有一個 CROSS APPLY 解決方案:
Just for fun here's a CROSS APPLY solution:
SELECT
B.Department,
DateAdd(month, (B.Year - 1900) * 12 + M.Mo - 1, 0) [Date],
M.Balance
FROM
dbo.Budget B
CROSS APPLY (
VALUES
(1, Month1), (2, Month2), (3, Month3), (4, Month4), (5, Month5), (6, Month6),
(7, Month7), (8, Month8), (9, Month9), (10, Month10), (11, Month11), (12, Month12)
) M (Mo, Balance);
它與@Aaron Bertrand 的 UNPIVOT 沒有什么不同，沒有使用 UNPIVOT.
It's really no different than @Aaron Bertrand's UNPIVOT, without using UNPIVOT.
如果您必須將日期作為字符串，則將字符串放入 CROSS APPLY 中，如 ('01', Month1) 并將 SELECT 更改為 Convert(char(4),B.Year) + M.Mo.
If you must have the date as a string, then put strings in the CROSS APPLY like ('01', Month1) and change the SELECT to Convert(char(4), B.Year) + M.Mo.
這篇關于如何從單行創建多行 - 規范化表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！