Multiple Column Pivot in T-SQL

by

Rewrite using MAX(CASE…) and GROUP BY:

select 
  field1
, [1] = max(case when RowID = 1 then field2 end)
, [2] = max(case when RowID = 2 then field2 end)
, [3] = max(case when RowID = 3 then field2 end)
, [4] = max(case when RowID = 4 then field2 end)
from (
  select 
    field1
  , field2
  , RowID = row_number() over (partition by field1 order by field2)
  from tblname
  ) SourceTable
group by 
  field1

From there you can add in field3, field4, etc.

More Related Contents:

  1. Comma separated results in SQL
  2. T-SQL stored procedure that accepts multiple Id values
  3. Understanding PIVOT function in T-SQL
  4. How do I escape a percentage sign in T-SQL?
  5. Using RegEx in SQL Server
  6. Why use a READ UNCOMMITTED isolation level?
  7. How do you count the number of occurrences of a certain substring in a SQL varchar?
  8. How much size “Null” value takes in SQL Server
  9. Find index of last occurrence of a sub-string using T-SQL
  10. Hidden Features of SQL Server
  11. SQL Server 2008 Empty String vs. Space
  12. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  13. SQL Server: combining multiple rows into one row
  14. Query to list all stored procedures
  15. SQL Server: Index columns used in like?
  16. WHERE clause on SQL Server “Text” data type
  17. Return all possible combinations of values on columns in SQL
  18. SQL Server error on update command – “A severe error occurred on the current command”
  19. Recursive sum in tree structure
  20. Convert decimal time to hours and minutes
  21. Joining multiple tables returns NULL value
  22. Which join syntax is better?
  23. Compute MD5 hash of a UTF8 string
  24. LPAD in SQL Server 2008
  25. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  26. How do I remove non-breaking spaces from a column in SQL server?
  27. How to wait for 2 seconds?
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment