数据透视表和连接列 [英] Pivot Table and Concatenate Columns

问题描述

我有一个数据库，格式如下：

  ID TYPE SUBTYPE COUNT MONTH 
 1 AZ 1 7 / 1/2008 
 1 AZ 3 7/1/2008 
 2 BC 2 7/2/2008 
 1 AZ 3 7/2/2008 
  
 
 
 
我可以使用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 
  
因此， TYPE ， SUBTYPE 连接到新列， ID 和 MONTH <$ c> / code>匹配。
 
 
 
任何提示都将不胜感激。在SQL中是可能的，还是应该手动编程？
 
 
 
数据库是SQL Server 2005. 
 
 
 
有 TYPES 和 SUBTYPES 的100个，因此'A'和'Z'不应该硬编码，动态。
解决方案
 SQL Server 2005提供了一个非常有用的PIVOT和UNPIVOT操作符，允许您使用PIVOT和一些代码生成/动态SQL 
  / * 
 CREATE TABLE [dbo]。[stackoverflow_159456]（
 [ID] [int] NOT NULL，
 [TYPE] [char]（1）NOT NULL，
 [SUBTYPE] int] NOT NULL，
 [MONTH] [datetime] NOT NULL 
）ON [PRIMARY] 
 * / 
 
 DECLARE @sql AS varchar（max）
 DECLARE @pivot_list AS varchar（max） - 为COALESCE技术保留NULL 
 DECLARE @select_list AS varchar（max） - 对于COALESCE技术保留NULL 
 
 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 ]）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）
  
 
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:
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, SUBTYPE are concatenated into new columns and COUNT is summed where the ID and MONTH match.

Any tips would be appreciated. Is this possible in SQL or should I program it manually?

The database is SQL Server 2005. 

Assume there are 100s of TYPES and SUBTYPES so and 'A' and 'Z' shouldn't be hard coded but generated dynamically.
解决方案
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
/*
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)


                        
这篇关于数据透视表和连接列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！