Pivot Table and Concatenate Columns
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)
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.