SQL Server Exact Table Transpose

Question

How do you achieve an exact transpose in SQL?

Month | High | Low | Avg
-------------------------
Jan   | 10    | 9   | 9.5
-------------------------
Feb   | 8  |  7   | 7.5
-------------------------
Mar   | 7    | 6   | 6.5
-------------------------

Result

------ Jan | Feb | Mar
--------------------------
High-- 10  | 8   | 7
--------------------------
Low--  9   | 7   | 6
--------------------------
Avg    9.5 | 7.5 | 6.5
--------------------------

Answer 1

In order to get the result, you will first have to unpivot the High, Low and Avg columns by turning those into rows. Then you will apply the pivot function to convert the month values into columns. (See: MSDN PIVOT/UNPIVOT docs)

Since you are using SQL Server 2008+, you can use CROSS APPLY and VALUES to unpivot. The code to unpivot is:

select t.month, 
  c.col, 
  c.value
from yourtable t
cross apply
(
  values ('High', high), ('Low', Low), ('Avg', Avg)
) c (col, value)

See SQL Fiddle with Demo. This gives the result in a format that can then be pivoted by month:

| MONTH |  COL | VALUE |
------------------------
|   Jan | High |    10 |
|   Jan |  Low |     9 |
|   Jan |  Avg |   9.5 |
|   Feb | High |     8 |
|   Feb |  Low |     7 |

Once the data is in rows, you apply the pivot function, so the code will be:

select col, Jan, Feb, Mar
from
(
  select t.month, 
    c.col, 
    c.value
  from yourtable t
  cross apply
  (
    values ('High', high), ('Low', Low), ('Avg', Avg)
  ) c (col, value)
) d
pivot
(
  sum(value)
  for month in (Jan, Feb, Mar)
) piv

See SQL Fiddle with Demo. This gives the result:

|  COL | JAN | FEB | MAR |
--------------------------
|  Avg | 9.5 | 7.5 | 6.5 |
| High |  10 |   8 |   7 |
|  Low |   9 |   7 |   6 |

Since you are pivoting month names, I doubt that you need a dynamic SQL version of this but if you had an unknown number of values, then you could use dynamic sql to get the result.