Transpose Column to Rows using SQL Server

Question

Could you help me one case, below

I have an original table, like this:

MONTH | YEAR | PO1 | PO2 | PO3

+++++++++++++++++++++++++++++++++++++++++

01      2010   100    20    10

then I want a result is like this:

DESCRIPTION | VALUE 
+++++++++++++++++++++++++++++++++++++++++
PO1            100
PO2             20
PO3             10

How should i do this in SQL Server, I really thanks for your advance.

Answer 1

You can use the UNPIVOT function to convert the columns into rows:

create table #test
(month int,year int,po1 int,po2 int ,po3 int)

insert into #test 
values
(5,2013,100,20,10)

select
  Description,
  value
from #test
unpivot
(
  value
  for Description in (po1, po2, po3)
) unpiv;

drop table #test

And taking from @bluefeet answer If you have multiple columns to unpivot then you can use the sql statement using dynamic SQL:

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

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'PO%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select 
       Description,
       value
     from yourtable
     unpivot
     (
        value
        for Description in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;