SQL 数据透视表和连接列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/159456/
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
Pivot Table and Concatenate Columns
提问by Brandon
I have a database in the following format:
我有一个以下格式的数据库：
ID TYPE SUBTYPE COUNT MONTH
1 A Z 1 7/1/2008
1 A Z 3 7/1/2008
2 B C 2 7/2/2008
1 A Z 3 7/2/2008
Can I use SQL to convert it into this:
我可以使用 SQL 将其转换为：
ID A_Z B_C MONTH
1 4 0 7/1/2008
2 0 2 7/2/2008
1 0 3 7/2/2008
So, the TYPE, SUBTYPEare concatenated into new columns and COUNTis summed where the IDand MONTHmatch.
因此，TYPE,SUBTYPE连接到新列中，并COUNT在ID和MONTH匹配的地方求和。
Any tips would be appreciated. Is this possible in SQL or should I program it manually?
任何提示将不胜感激。这在 SQL 中是可能的还是我应该手动编程？
The database is SQL Server 2005.
数据库是 SQL Server 2005。
Assume there are 100s of TYPESand SUBTYPESso and 'A' and 'Z' shouldn't be hard coded but generated dynamically.
假设有数百TYPES和SUBTYPES因此与“A”和“Z”不应该被硬编码，但动态生成的。
回答by Cade Roux
SQL Server 2005 offers a very useful PIVOT and UNPIVOT operator which allow you to make this code maintenance-free using PIVOT and some code generation/dynamic SQL
SQL Server 2005 提供了一个非常有用的 PIVOT 和 UNPIVOT 运算符，允许您使用 PIVOT 和一些代码生成/动态 SQL 使此代码免维护
/*
CREATE TABLE [dbo].[stackoverflow_159456](
[ID] [int] NOT NULL,
[TYPE] [char](1) NOT NULL,
[SUBTYPE] [char](1) NOT NULL,
[COUNT] [int] NOT NULL,
[MONTH] [datetime] NOT NULL
) ON [PRIMARY]
*/
DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique
SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + PIVOT_CODE + ']'
,@select_list = COALESCE(@select_list + ', ', '') + 'ISNULL([' + PIVOT_CODE + '], 0) AS [' + PIVOT_CODE + ']'
FROM (
SELECT DISTINCT [TYPE] + '_' + SUBTYPE AS PIVOT_CODE
FROM stackoverflow_159456
) AS PIVOT_CODES
SET @sql = '
;WITH p AS (
SELECT ID, [MONTH], [TYPE] + ''_'' + SUBTYPE AS PIVOT_CODE, SUM([COUNT]) AS [COUNT]
FROM stackoverflow_159456
GROUP BY ID, [MONTH], [TYPE] + ''_'' + SUBTYPE
)
SELECT ID, [MONTH], ' + @select_list + '
FROM p
PIVOT (
SUM([COUNT])
FOR PIVOT_CODE IN (
' + @pivot_list + '
)
) AS pvt
'
EXEC (@sql)
回答by Bob Probst
select id,
sum(case when type = 'A' and subtype = 'Z' then [count] else 0 end) as A_Z,
sum(case when type = 'B' and subtype = 'C' then [count] else 0 end) as B_C,
month
from tbl_why_would_u_do_this
group by id, month
You change requirements more than our marketing team! If you want it to be dynamic you'll need to fall back on a sproc.
您比我们的营销团队更能改变需求！如果您希望它是动态的，则需要依靠 sproc。
