问题描述
我对 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.
这篇关于如何从单行创建多行 - 规范化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！