How can I query row data as columns?

Question

I'm sure I'm missing something 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'd like to query the top 3 results and Pivot them as columns, so the end result set 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 can I accomplish this in SQL Server 2005?

I have been attempting this using PIVOT, but I am still very unfamiliar with that keyword and cannot get it to 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 data set is a bit more complex than this, and I need the top 10 records, not the top 3, so I don't want to simply do CASE WHEN RowNumber = X THEN... for each one.

Update

I tested all the answers below, and found most of them seem about the same with no apparent performance difference in smaller data sets (around 3k records), however there was a slight difference when running the queries against larger data sets.

Here are the results of my tests using 80,000 records and querying for 5 columns in the top 10 rows, so my end result set was 50 columns + the Id column. I'd suggest you test them on your own to decide which one works best for you and your environment.

• bluefoot's answer of unpivoting and re-pivoting the data averaged the fastest at about 12 seconds. I also liked this answer because I found it easiest to read and maintain.

• Aaron's answer and koderoid's answer both suggest using a MAX(CASE WHEN RowNumber = X THEN ...), and was close behind averaging at around 13 seconds.

• Rodney's answer of using multiple PIVOT statements averaged around 16 seconds, although it might be faster with fewer PIVOT statements (my tests had 5).

• And the first half of Aaron's answer that suggested using a CTE and OUTER APPLY was the slowest. I don't know how long it would take to run because I cancelled it after 2 minutes, and that was with around 3k records, 3 rows, and 3 columns instead of 80k records, 10 rows, and 5 columns.

Answer 1

You can do an UNPIVOT and then a PIVOT of the data. this can be done either 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 demo

Dynamic Version, this will get the list of columns to unpivot and then to pivot at run-time:

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, however the dynamic is great if you do not know the number of columns ahead of time.

The Dynamic version is working under the assumption that the rownumber is already a part of the dataset.

Answer 2

You can try to do the pivot in three separate pivot statements. Please give this a 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 scope of the criteria for selecting the top ten records, but this produces and output that may get you closer to your answer.

SQL Fiddle Example