Pivot data in T-SQL

by

If you are using SQL Server 2005 (or above), here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Month])
                        FROM YourTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Person, Month, Paid
       FROM YourTable ) base
       PIVOT (Sum(Paid) FOR [Person]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This will work no matter how many different status you have. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

Other examples:

More Related Contents:

  1. SQL Server dynamic PIVOT query?
  2. Simple way to transpose columns and rows in SQL?
  3. T-SQL dynamic pivot
  4. TSQL Pivot without aggregate function
  5. Dynamically create columns sql
  6. TSQL PIVOT MULTIPLE COLUMNS
  7. Dynamic Pivot Columns in SQL Server
  8. Using PIVOT in SQL Server 2008
  9. SQL transpose full table
  10. Truncate (not round) decimal places in SQL Server
  11. Select statement to find duplicates on certain fields
  12. Getting only Month and Year from SQL DATE
  13. Is it possible to select sql server data using column ordinal position
  14. Selecting N rows in SQL Server
  15. How can I INSERT data into two tables simultaneously in SQL Server?
  16. SQL “select where not in subquery” returns no results
  17. How can a LEFT OUTER JOIN return more records than exist in the left table?
  18. How to create a PivotTable in Transact/SQL?
  19. Dynamic SQL to generate column names?
  20. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  21. Calculate the number of records for each date between 2 dates
  22. T-SQL: How to use parameters in dynamic SQL?
  23. How to compare datetime with only date in SQL Server
  24. Merge overlapping date intervals
  25. SQL server select distinct rows using most recent value only
  26. Split words with a capital letter in sql
  27. SQL poor stored procedure execution plan performance – parameter sniffing
  28. Eliminate and reduce overlapping date ranges
  29. scope_identity vs ident_current
  30. Cumulative Total in MS Sql server [duplicate]

Leave a Comment