Pivot Table and Concatenate Columns

by

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)

More Related Contents:

  1. Is it possible to use greater than equal to operator in group by? [closed]
  2. Simple way to transpose columns and rows in SQL?
  3. How can I schedule a job to run a SQL query daily?
  4. What’s the best practice for primary keys in tables? [closed]
  5. Reset identity seed after deleting records in SQL Server
  6. T-SQL dynamic pivot
  7. Querying data by joining two tables in two database on different servers
  8. Generate a resultset of incrementing dates in TSQL
  9. TSQL Pivot without aggregate function
  10. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  11. What is the difference between using a cross join and putting a comma between the two tables?
  12. How would you implement sequences in Microsoft SQL Server?
  13. What does =* mean?
  14. Correct use of transactions in SQL Server
  15. Splitting the string in sql server
  16. Dynamic Pivot Table in SQL Server
  17. Need to list all triggers in SQL Server database with table name and table’s schema
  18. How can I do a BEFORE UPDATED trigger with sql server?
  19. Favourite performance tuning tricks [closed]
  20. Deferrable Constraints in SQL Server
  21. How to check which locks are held on a table
  22. How to sum up time field in SQL Server
  23. Can SQL Server Express LocalDB be connected to remotely?
  24. Using SQL Server as a DB queue with multiple clients
  25. SQL Server search using like while ignoring blank spaces
  26. What does ON [PRIMARY] mean?
  27. How do I change db schema to dbo
  28. Functions vs Stored Procedures
  29. Space used by nulls in database
  30. Single or multiple databases

Leave a Comment