交叉表或转置查询

Question

我有一个来自如下查询的结果集

mon-yar Count   EB      VC
Apr-11    34    1237    428
May-11    54    9834    87
Jun-11    23    9652    235
Jul-11    567   10765   1278
Aug-11    36    10234   1092
Sep-11    78    8799    987
Oct-11    23    10923   359
Nov-11    45    11929   346
Dec-11    67    9823    874
Jan-12    45    2398    245
Feb-12    90    3487    937
Mar-12   123    7532    689
Apr-12   109    1256    165

我所希望的是：

monthyear   Apr-11  May-11  Jun-11  Jul-11  Aug-11  Sep-11  Oct-11  Nov-11  Dec-11  Jan-12  Feb-12  Mar-12  Apr-12
Count           34      54      23     567      36      78      23      45      67      45      90     123     109
EB            1237    9834    9652   10765   10234    8799   10923   11929    9823    2398    3487    7532    1256
VC             428      87     235    1278    1092     987     359     346     874     245     937     689     165

月份和年份的值是动态的。我如何才能以这种方式生成它？

Answer 1

如果您不想使用PIVOT，您可以使用下面的解决方案，只要您不介意在结果时在Excel中使用文本到列。

如果你要运行：

with tbl as(
select 'Apr-11' as monyar, 34 as cnt, 1237 as eb, 428 as vc from dual union all
select 'May-11' as monyar, 54 as cnt, 9834 as eb, 87 as vc from dual union all
select 'Jun-11' as monyar, 23 as cnt, 9652 as eb, 235 as vc from dual union all
select 'Jul-11' as monyar, 567 as cnt, 10765 as eb, 1278 as vc from dual union all
select 'Aug-11' as monyar, 36 as cnt, 10234 as eb, 1092 as vc from dual union all
select 'Sep-11' as monyar, 78 as cnt, 8799 as eb, 987 as vc from dual union all
select 'Oct-11' as monyar, 23 as cnt, 10923 as eb, 359 as vc from dual union all
select 'Nov-11' as monyar, 45 as cnt, 11929 as eb, 346 as vc from dual union all
select 'Dec-11' as monyar, 67 as cnt, 9823 as eb, 874 as vc from dual union all
select 'Jan-12' as monyar, 45 as cnt, 2398 as eb, 245 as vc from dual union all
select 'Feb-12' as monyar, 90 as cnt, 3487 as eb, 937 as vc from dual union all
select 'Mar-12' as monyar, 123 as cnt, 7532 as eb, 689 as vc from dual union all
select 'Apr-12' as monyar, 109 as cnt, 1256 as eb, 165 as vc from dual
)
select 'Month' as lbl, listagg(monyar,' | ') within group (order by monyar) as list from tbl
union all
select 'Count' as lbl, listagg(cnt,' | ') within group (order by monyar) as list from tbl
union all
select 'EB' as lbl, listagg(eb,' | ') within group (order by monyar) as list from tbl
union all
select 'VC' as lbl, listagg(vc,' | ') within group (order by monyar) as list from tbl

结果：

LBL     LIST
Month   Apr-11 | Apr-12 | Aug-11 | Dec-11 | Feb-12 | Jan-12 | Jul-11 | Jun-11 | Mar-12 | May-11 | Nov-11 | Oct-11 | Sep-11
Count   34 | 109 | 36 | 67 | 90 | 45 | 567 | 23 | 123 | 54 | 45 | 23 | 78
EB      1237 | 1256 | 10234 | 9823 | 3487 | 2398 | 10765 | 9652 | 7532 | 9834 | 11929 | 10923 | 8799
VC      428 | 165 | 1092 | 874 | 937 | 245 | 1278 | 235 | 689 | 87 | 346 | 359 | 987

使用管道作为分隔符，然后可以将第二列拆分为任意数量的列。

LISTAGG是一个Oracle函数，我不确定在sql server中是否有1:1的等价物，因此如果它必须在sql server中运行，您将不得不以某种方式模拟垂直连接。