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.
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;
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)
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 |