How can I query for row data in columns?

Question

I'm sure something is missing here.

I have a dataset like this:

  FK RowNumber Value Type Status
 1 1 aaaaa A New
 1 2 bbbbb B Good
 1 3 ccccc A Bad
 1 4 ddddd C Good
 1 5 eeeee B Good
 2 1 fffff C Bad
 2 2 ggggg A New
 2 3 hhhhh C Bad
 3 1 iiiii A Good
 3 2 jjjjj A Good

I would like to query the results from the top 3 and collapse them as columns, so the set of final results looks like this:

  FK Value1 Type1 Status1 Value2 Type2 Status2 Value3 Type3 Status3
 1 aaaaa A New bbbbb B Good ccccc A Bad
 2 fffff C Bad ggggg A New hhhhh C Bad
 3 iiiii A Good jjjjj A Good

How to do this in SQL Server 2005?

I am trying to use this PIVOT , but I am still very unfamiliar with this keyword and cannot make it work the way I want.

SELECT * --Id, [1], [2], [3] FROM ( SELECT Id, Value, Type, Status , ROW_NUMBER() OVER (PARTITION BY Id ORDER Status, Type) as [RowNumber] FROM MyTable ) as T PIVOT ( -- I know this section doesn't work. I'm still trying to figure out PIVOT MAX(T.Value) FOR RowNumber IN ([1], [2], [3]), MAX(T.Type) FOR RowNumber IN ([1], [2], [3]), MAX(T.Status) FOR RowNumber IN ([1], [2], [3]) ) AS PivotTable; 

My actual dataset is a bit more complicated than this, and I need the first 10 records, not the top 3, so I don’t want to just do CASE WHEN RowNumber = X THEN... for each of them.

Update

I tested all of the answers below and found that most of them look approximately the same, with no apparent difference in performance in smaller datasets (about 3 thousand records), however, when performing queries against large datasets, there was a slight difference.

Here are the results of my tests using 80,000 records and queries for 5 columns in the top 10 rows, so my final result was 50 columns + Id column. I would suggest you test them yourself to decide which one is best for you and your environment.

• The answer to the question about the blue fight , showing the deployment and re-rotation of the data, averaged the fastest by 12 seconds. I also liked this answer because it was easier for me to read and maintain.

• Aaron's answer and koderoid answer both suggest using MAX(CASE WHEN RowNumber = X THEN ...) , and were close after averaging about 13 seconds.

• Rodney's answer using multiple PIVOT expressions averaged over 16 seconds, although this can be faster with fewer PIVOT statements (my tests had 5).

• And the first half of Aaron's answer, which suggested using CTE and OUTER APPLY , was the slowest. I don’t know how long it will take to start, because I canceled it after 2 minutes, and that was with about 3 thousand records, 3 rows and 3 columns instead of 80 thousand records, 10 rows and 5 columns.

Answer 1

You can do UNPIVOT and then PIVOT data. this can be done statically or dynamically:

Static version:

 select * from ( select fk, col + cast(rownumber as varchar(1)) new_col, val from ( select fk, rownumber, value, cast(type as varchar(10)) type, status from yourtable ) x unpivot ( val for col in (value, type, status) ) u ) x1 pivot ( max(val) for new_col in ([value1], [type1], [status1], [value2], [type2], [status2], [value3], [type3]) ) p 

see SQL Fiddle with a demo

Dynamic version, this will get a list of UNPIVOT columns and then PIVOT at runtime:

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('yourtable') and C.name not in ('fk', 'rownumber') for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rownumber as varchar(10))) from yourtable t cross apply sys.columns as C where C.object_id = object_id('yourtable') and C.name not in ('fk', 'rownumber') group by c.name, t.rownumber order by t.rownumber FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select fk, col + cast(rownumber as varchar(10)) new_col, val from ( select fk, rownumber, value, cast(type as varchar(10)) type, status from yourtable ) x unpivot ( val for col in ('+ @colsunpivot +') ) u ) x1 pivot ( max(val) for new_col in ('+ @colspivot +') ) p' exec(@query) 

see SQL Fiddle with Demo

Both will generate the same results, but the dynamics are great if you do not know the number of columns ahead of time.

The dynamic version works under the assumption that ripples are already part of the data set.

Answer 2

You can try to rotate in three separate summary statements. Try:

 SELECT Id ,MAX(S1) [Status 1] ,MAX(T1) [Type1] ,MAX(V1) [Value1] --, Add other columns FROM ( SELECT Id, Value , Type, Status , 'S' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Status_RowNumber] , 'T' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Type_RowNumber] , 'V' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Value_RowNumber] FROM MyTable ) as T PIVOT ( MAX(Status) FOR Status_RowNumber IN ([S1], [S2], [S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10]) )AS StatusPivot PIVOT( MAX(Type) FOR Type_RowNumber IN ([T1], [T2], [T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10]) )AS Type_Pivot PIVOT( MAX(Value) FOR Value_RowNumber IN ([V1], [V2], [V3],[V4],[V5],[V6],[V7],[V8],[V9],[V10]) )AS Value_Pivot GROUP BY Id 

I don’t know the full range of criteria for choosing the top ten entries, but this creates and outputs what may come close to your answer.

SQL script example

Answer 3

Rodney muli-pivot is smart, that's for sure. Here are two other alternatives that are, of course, less attractive when you fall into the 10X and 3X areas.

 ;WITH a AS ( SELECT Id, Value, Type, Status, n = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Status], [Type]) FROM dbo.MyTable ) SELECT a.Id, Value1 = a.Value, Type1 = a.[Type], Status1 = a.[Status], Value2 = b.Value, Type2 = b.[Type], Status2 = b.[Status], Value3 = c.Value, Type3 = c.[Type], Status3 = c.[Status] FROM a OUTER APPLY (SELECT * FROM a AS T2 WHERE n = an + 1 AND id = a.id) AS b OUTER APPLY (SELECT * FROM a AS T2 WHERE n = bn + 1 AND id = b.id) AS c WHERE an = 1 ORDER BY a.Id; 

- or -

 ;WITH a AS ( SELECT Id, Value, [Type], [Status], n = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Status], [Type]) FROM dbo.MyTable ) SELECT Id, Value1 = MAX(CASE WHEN n = 1 THEN Value END), Type1 = MAX(CASE WHEN n = 1 THEN [Type] END), Status1 = MAX(CASE WHEN n = 1 THEN [Status] END), Value2 = MAX(CASE WHEN n = 2 THEN Value END), Type2 = MAX(CASE WHEN n = 2 THEN [Type] END), Status2 = MAX(CASE WHEN n = 2 THEN [Status] END), Value3 = MAX(CASE WHEN n = 3 THEN Value END), Type3 = MAX(CASE WHEN n = 3 THEN [Type] END), Status3 = MAX(CASE WHEN n = 3 THEN [Status] END) FROM a GROUP BY Id ORDER BY a.Id; 

Answer 4

This may work for you, although it is not elegant.

 select aa.FK_Id , isnull(max(aa.Value1), '') as Value1 , isnull(max(aa.Type1), '') as Type1 , isnull(max(aa.Status1), '') as Status1 , isnull(max(aa.Value2), '') as Value2 , isnull(max(aa.Type2), '') as Type2 , isnull(max(aa.Status2), '') as Status2 , isnull(max(aa.Value3), '') as Value3 , isnull(max(aa.Type3), '') as Type3 , isnull(max(aa.Status3), '') as Status3 from ( select FK_Id , case when RowNumber = 1 then Value else null end as Value1 , case when RowNumber = 1 then [Type] else null end as Type1 , case when RowNumber = 1 then [Status] else null end as Status1 , case when RowNumber = 2 then Value else null end as Value2 , case when RowNumber = 2 then [Type] else null end as Type2 , case when RowNumber = 2 then [Status] else null end as Status2 , case when RowNumber = 3 then Value else null end as Value3 , case when RowNumber = 3 then [Type] else null end as Type3 , case when RowNumber = 3 then [Status] else null end as Status3 from Table1 ) aa group by aa.FK_Id 

Answer 5

try something like this:

 declare @rowCount int set @rowCount = 10 declare @isNullClause varchar(4024) set @isnullClause = '' declare @caseClause varchar(4024) set @caseClause = '' declare @i int set @i = 1 while(@i <= @rowCount) begin set @isnullClause = @isNullClause + ' , max(aa.Value' + CAST(@i as varchar(3)) + ') as Value' + CAST(@i as varchar(3)) + ' , max(aa.Type' + CAST(@i as varchar(3)) + ') as Type' + CAST(@i as varchar(3)) + ' , max(aa.Status' + CAST(@i as varchar(3)) + ') as Status' + CAST(@i as varchar(3)) + ' '; set @caseClause = @caseClause + ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Value else null end as Value' + CAST(@i as varchar(3)) + ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Type else null end as Type' + CAST(@i as varchar(3)) + ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Status else null end as Status' + CAST(@i as varchar(3)) + ' ' set @i = @i + 1; end declare @sql nvarchar(4000) set @sql = 'select aa.FK_Id ' + @isnullClause + ' from ( select FK_Id ' + @caseClause + ' from Table1) aa group by aa.FK_Id ' exec SP_EXECUTESQL @sql