Switching rows for columns and columns for rows

Question

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?

Check this question http://stackoverflow.com/questions/13092867/sql-server-2008-convert-column-value-to-row — Mandeep Singh, Apr 19 '13 at 08:43
http://stackoverflow.com/questions/9547485/swap-some-rows-into-columns-in-sql-server-2008 — Mandeep Singh, Apr 19 '13 at 08:46

Taryn · Accepted Answer · 2013-04-19T09:49:27.430

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 |

Switching rows for columns and columns for rows

1 Answers1