Multiple Column Pivot in T-SQL

Question

I am working with a table where there are multiple rows that I need pivoted into columns. So the pivot is the perfect solution for this, and works well when all I need is one field. I am needing to return several fields based upon the pivot. Here is the pseudo code with specifics stripped out:

SELECT 
  field1,
  [1], [2], [3], [4]
FROM
  (
  SELECT 
    field1, 
    field2, 
    (ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)) RowID
  FROM tblname
  ) AS SourceTable
PIVOT
  (
  MAX(field2)
  FOR RowID IN ([1], [2], [3], [4])
  ) AS PivotTable;

The above syntax works brilliantly, but what do I do when I need to get additional information found in field3, field4....?

Answer 1

I am unsure if you are using MS SQL Server, but if you are... You may want to take a look at the CROSS APPLY functionality of the engine. Basically it will allow you to apply the results of a table-valued UDF to a result set. This would require you to put your pivot query into a table-valued result set.

http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

Answer 2

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.

Answer 3

I have requirement even if more complicate than multiple column pivot. The case is: 1) there are 3 fields need to be moved from rows into columns. 2) for solve 3 fields issue, instead of using pivot I have to use Case When... However it doesn't work either, 3) for using case when, it needs group by. However there are multiple rows in each columns segment(after been pivoted), and the different value of pivoted columns should fits into same row at the end.

Thank you

Answer 4

wrap your sql statement with something like:

select a.segment, sum(field2), sum(field3) from (original select with case arguments) a group by a.segment

It should collapse your results into one row, grouped on field1.