CODEWITHSUNDEEP
sqlsql-server-2008pivotunpivot
Yairt
Yairt

Reputation: 167

sql server group columns as rows (pivot?)

I've got this result data in SQL Server 2008 R2

id      Size    Acted   Sum     Avg1    Avg2    A1       A2      A3
1       3921    39      690     17.69   0.18    NULL      NULL   NULL
40      11979   301     5944.26 19.75   0.5 10000.00 2000.00 1000.00
41      11714   289     5060    17.51   0.43 10000.00 3000.00 2000.00
42      11599   265     4107.98 15.5    0.35 10000.00 5000.00 500.00

And I would like to move the columns into rows according to the id so I will recieve this result:

id1        id40       id41         id42
1          40         41           42
3921       11979      11714        11599
39         301        289          265
690        5944       5060         4107
17.69      19.75      17.51        15.5
0.18       0.5        0.43         0.35
           10000.00   2000.00      1000.00
           10000.00   3000.00      2000.00
           10000.00   5000.00      500.00

Is there a way to do that? I tried pivot but as far as I tried I could only transform 1 column and not many as needed in this case.

Upvotes: 0

Views: 2204

Answers (1)

Taryn
Taryn

Reputation: 247720

In order to get this result, you will want to first unpivot the data from the columns to rows, and then apply the PIVOT function.

Since you are using SQL Server 2008, you can use CROSS APPLY and VALUES to unpivot the data. This takes the values from your numerous columns and converts them to rows:

select 'id'+cast(t.id as varchar(10)) p_id,
  c.col, 
  c.value,
  c.sort_order
from yourtable t
cross apply
(
  values 
    (1, 'id', id),
    (2, 'size', size),
    (3, 'acted', acted),
    (4, 'sum', sum),
    (5, 'avg1', avg1),
    (6, 'avg2', avg2),
    (7, 'a1', a1),
    (8, 'a2', a2),
    (9, 'a3', a3)
) c (sort_order, col, value)

See SQL Fiddle with Demo. Once the data has been unpivoted, then you can pivot using the new columns which are the id values. So the full code is:

select col, 
  id1, 
  id40, 
  id41, 
  id42
from
(
  select 'id'+cast(t.id as varchar(10)) p_id,
    c.col, 
    c.value,
    c.sort_order
  from yourtable t
  cross apply
  (
    values 
      (1, 'id', id),
      (2, 'size', size),
      (3, 'acted', acted),
      (4, 'sum', sum),
      (5, 'avg1', avg1),
      (6, 'avg2', avg2),
      (7, 'a1', a1),
      (8, 'a2', a2),
      (9, 'a3', a3)
  ) c (sort_order, col, value)
) src
pivot
(
  max(value)
  for p_id in (id1, id40, id41, id42)
) piv
order by sort_order;

See SQL Fiddle with Demo.

If you cannot use the CROSS APPLY and VALUES, then this can also be done, using the UNPIVOT function:

select col, 
  id1, id40, id41, id42
from
(
  select 'id'+cast(id_piv as varchar(10)) id,
    col,
    value,
    case col
      when 'id' then 1
      when 'size' then 2
      when 'acted' then 3
      when 'sum' then 4
      when 'avg1' then 5
      when 'avg2' then 6
      when 'a1' then 7
      when 'a2' then 8
      when 'a3' then 9 end sort_order
  from
  (
    select id id_piv,
      cast(id as numeric(10, 2)) id, 
      cast(size as numeric(10, 2)) size,
      cast(acted as numeric(10, 2)) acted,
      sum, avg1, avg2, A1, A2, A3
    from yourtable
  ) d
  unpivot
  (
    value
    for col in (id, size, acted, sum, avg1, avg2, a1, a2, a3)
   ) unpiv
) src
pivot
(
  max(value)
  for id in (id1, id40, id41, id42)
) piv
order by sort_order;

See SQL Fiddle with Demo

Finally, if you are going to have an unknown number of id values that you want to convert to columns, then you will need to use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME('id'+cast(id as varchar(10))) 
                    from yourtable
                    group by id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col, ' + @cols + ' 
              from 
             (
                select ''id''+cast(t.id as varchar(10)) p_id,
                  c.col, 
                  c.value,
                  c.sort_order
                from yourtable t
                cross apply
                (
                  values 
                    (1, ''id'', id),
                    (2, ''size'', size),
                    (3, ''acted'', acted),
                    (4, ''sum'', sum),
                    (5, ''avg1'', avg1),
                    (6, ''avg2'', avg2),
                    (7, ''a1'', a1),
                    (8, ''a2'', a2),
                    (9, ''a3'', a3)
                ) c (sort_order, col, value)
            ) x
            pivot 
            (
                max(value)
                for p_id in (' + @cols + ')
            ) p 
            order by sort_order'

execute(@query)

See SQL Fiddle with Demo

All versions the result:

|   COL |    ID1 |    ID40 |  ID41 |    ID42 |
----------------------------------------------
|    id |      1 |      40 |    41 |      42 |
|  size |   3921 |   11979 | 11714 |   11599 |
| acted |     39 |     301 |   289 |     265 |
|   sum |    690 | 5944.26 |  5060 | 4107.98 |
|  avg1 |  17.69 |   19.75 | 17.51 |    15.5 |
|  avg2 |   0.18 |     0.5 |  0.43 |    0.35 |
|    a1 | (null) |   10000 | 10000 |   10000 |
|    a2 | (null) |    2000 |  3000 |    5000 |
|    a3 | (null) |    1000 |  2000 |     500 |

Upvotes: 4

Related Questions