基于多列的SQL Pivot [英] SQL Pivot based on multiple columns

问题描述

我是SQL透视的新手

我有不同的要求，其中我需要根据程序类别基于多个列(Q1，Q2，Q3，Q4)旋转数据.请指教我该如何实现?

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?

所有示例均基于单个列(例如，SUM(Q1)FOR CategoryID IN([人]，[IT]，[旅行])

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

我如何根据Q1，Q2，Q3，Q4对其进行透视?

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

输入

输出

推荐答案

为了获得所需的结果，必须同时应用UNPIVOT和PIVOT函数.

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

unpivot 函数会将您的Q1，Q2，Q3和Q4列转换为行.完成后，将应用 pivot 函数.

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

请参见带演示的SQL提琴.得到的结果是:

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

请参见带演示的SQL提琴.结果如下:

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

这篇关于基于多列的SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！