I have query like this:
SELECT
Column,
SUM(Row1) AS Row1,
SUM(Row2) AS Row2,
SUM(Row3) AS Row3,
SUM(Row4) AS Row4,
SUM(Row5) AS Row5,
SUM(Row6) AS Row6,
SUM(Row7) AS Row7,
SUM(Row8) AS Row8,
SUM(Row9) AS Row9,
SUM(Row10) AS Row10,
SUM(Row11) AS Row11,
SUM(Row12) AS Row12,
SUM(Row13) AS Row13,
SUM(Row14) AS Row14,
SUM(Row15) AS Row15,
SUM(Row16) AS Row16,
SUM(Row17) AS Row17
FROM #temp
GROUP BY
Column
I get result like this:
Column Row1 Row2
----------- --------------------------------------- ---------------------------------------
1 45.00 0.00
2 19.00 0.00
And would like to get this:
Row 1 2
----------- --------------------------------------- ---------------------------------------
Row1 45.00 19.00
Row2 0.00 0.00
But the column number can vary, and the syntax has to be compatible with sql server 2005. How can I achive something like this?
Based on your current query and that you want the column values at a column, then my suggestion would be to apply both the UNPIVOT and then the PIVOT function.
The UNPIVOT function will take the multiple row1, row2, etc columns and convert them into multiple rows. Then you can take values and convert them to columns.
The query will be:
select row, [1], [2]
from
(
select [column], [row], value
from #temp
unpivot
(
value
for row in (row1, row2, row3, row4, row5, row6,
row7, row8, row9, row10, row11, row12,
row13, row14, row15, row16, row17)
) un
) src
pivot
(
sum(value)
for [column] in ([1], [2])
) piv;
See SQL Fiddle with Demo.
The above version will work great if you have a known number of values but if the values are unknown, then you will need to use dynamic SQL to both PIVOT and UNPIVOT:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yt') and
C.name != 'column'
for xml path('')), 1, 1, '')
select @cols = STUFF((SELECT distinct ',' + QUOTENAME([column])
from yt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select row, '+@cols+'
from
(
select [column], [row], value
from yt
unpivot
(
value
for [row] in ('+ @colsunpivot +')
) u
) src
pivot
(
sum(value)
for [column] in ('+@cols+')
)piv'
exec(@query);
See SQL Fiddle with Demo. Both give the result:
| ROW | 1 | 2 |
--------------------
| row1 | 55 | 93 |
| row2 | 112 | 21 |
| row3 | 523 | 24 |
| row4 | 665 | 179 |