T-sql pivot function

Question

I need to convert the following table

quarter   cal_year  blue    green   yellow  red

DEC 2011        +31%    25-30%  22-24%  -21%

MAR 2012        +61%    50-60%  43-49%  -42%

into this. Is there a simple way to achieve it?

Color   DEC     MAR     
blue    +31%    +61%    
green   25-30%  50-60%  
yellow  22-24%  43-49%  
red     -21%    -42%    

Answer 1

While @Joro's version will work, I would do this slightly different since CTE is not needed in this case.

Static Version of the PIVOT where you know the columns to transform:

select col, [Mar], [Dec]
from 
(
  select quarter, val, col
  from yourtable
  unpivot
  (
    val
    for col in (blue, green, yellow, red)
  )u
) x
pivot
(
  max(val)
  for quarter in ([Mar], [Dec])
) p

see SQL Fiddle with Demo

Dynamic Version where the columns are determined at run-time:

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

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(Quarter) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable') and
               C.name not in ('Quarter', 'cal_year')
         for xml path('')), 1, 1, '')

set @query 
  = 'select *
      from
      (
        select quarter, val, col
        from yourtable
        unpivot
        (
          val
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for quarter in ('+ @colspivot +')
      ) p'

exec(@query)

see SQL Fiddle with Demo

If you only have a few columns, then you can also do this with a CASE statement and a UNION ALL

select col,
  max(case when quarter = 'MAR' then val end) MAR,
  max(case when quarter = 'DEC' then val end) DEC
from
(
  select quarter, val, col
  from
  (
    select quarter, blue as val, 'blue' as col
    from yourtable
    union all
    select quarter, green as val, 'green' as col
    from yourtable
    union all
    select quarter, yellow as val, 'yellow' as col
    from yourtable
    union all
    select quarter, red as val, 'red' as col
    from yourtable
  ) u
) x
group by col

see SQL Fiddle with Demo