[Solved] SQL Pivot based on multiple columns

Question

In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions.

The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function.

The unpivot code will be similar to this:

select programid,
  col + '_'+ category cat_col,
  value
from yourtable 
unpivot
(
  value
  for col in (Q1, Q2, Q3, Q4)
) unpiv

See SQL Fiddle with Demo. This gives a result:

| PROGRAMID |   CAT_COL |  VALUE |
----------------------------------
|       366 | Q1_People | 109034 |
|       366 | Q2_People |  25418 |
|       366 | Q3_People | 101130 |
|       366 | Q4_People |  54787 |

You can see that this query creates a new column name to pivot which has the category value and the quarter name.

Once you have this result, you can apply the pivot function:

select *
from
(
  select programid,
    col + '_'+ category cat_col,
    value
  from yourtable 
  unpivot
  (
    value
    for col in (Q1, Q2, Q3, Q4)
  ) unpiv
) d
pivot
(
  sum(value)
  for cat_col in (Q1_People, Q2_People, Q3_People, Q4_People,
                  Q1_IT, Q2_IT, Q3_IT, Q4_IT,
                  Q1_Travel, Q2_Travel, Q3_Travel, Q4_Travel)
) piv

See SQL Fiddle with Demo. This gives the result:

| PROGRAMID | Q1_PEOPLE | Q2_PEOPLE | Q3_PEOPLE | Q4_PEOPLE | Q1_IT | Q2_IT | Q3_IT | Q4_IT | Q1_TRAVEL | Q2_TRAVEL | Q3_TRAVEL | Q4_TRAVEL |
---------------------------------------------------------------------------------------------------------------------------------------------
|       366 |    109034 |     25418 |    101130 |     54787 |     0 |     0 |     0 |     0 |      1195 |       613 |      1113 |      1195 |

Accepted Answer