Search code examples
sql-servert-sqlpivotunpivot

SQL Pivot based on multiple columns

Hi I am New to SQL Pivoting

I have different requirement where i need to Pivot data based on multiple columns(Q1,Q2,Q3,Q4) based on Categories for Programs. Please advice how can I achieve this?

All examples are based on a single column( eg. SUM(Q1) FOR CategoryID IN ([People], [IT], [Travel])

How do i pivot it based on Q1, Q2, Q3, Q4?

Input

Input

Output

Result

Solution

  • 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 |