ABAPSQL 中实现行列转换
前言
关于行列转换的定义，相信接触过SQL的同学都不陌生，具体可见下图。
PIVOT多行转多列； UNPIVOT多列转多行
数据准备
本文将以FI模块的常用表 新总账汇总表(FAGLFLEXT)中的数据作为测试原始数据。
创建序列表
需要创建一张填充序列号的表 ZTSERIES，用途将在后面的SQL语句中体现出来。只需要两个字段MANDT和NUM
填充序列表
利用SE16N或者写程序，将数字1到100填充到序列表 Z TSERIES中
实现列转行UNPIVOT
"UNPIVOT SELECT a~ryear,a~racct,a~rbukrs,a~drcrk,a~activ,a~awtyp, concat( 'HSL',lpad( CAST( b~num AS CHAR ),2,'0' ) ) AS col_name, CASE b~num WHEN 1 THEN hsl01 WHEN 2 THEN hsl02 WHEN 3 THEN hsl03 WHEN 4 THEN hsl04 WHEN 5 THEN hsl05 WHEN 6 THEN hsl06 WHEN 7 THEN hsl07 WHEN 8 THEN hsl08 WHEN 9 THEN hsl09 WHEN 10 THEN hsl10 WHEN 11 THEN hsl11 WHEN 12 THEN hsl12 WHEN 13 THEN hsl13 WHEN 14 THEN hsl14 WHEN 15 THEN hsl15 WHEN 16 THEN hsl16 END AS col_value FROM v_faglflext_view AS a JOIN ztseries AS b ON b~num <= 16 ORDER BY a~ryear,a~racct,a~rbukrs,a~drcrk,a~activ,b~num INTO TABLE @DATA(lt_data_unpivot) UP TO 160 ROWS.
cl_demo_output=>display( lt_data_unpivot ).效果如下：
实现行转列PIVOT
我们可以直接用步骤5中定义的内表 lt_data_unpivot 中的数据作为数据源，就不需要额外的准备测试数据了。
"PIVOT SELECT ryear,racct,rbukrs,drcrk,activ,awtyp, MAX( CASE col_name WHEN 'HSL01' THEN col_value END ) AS hsl01, MAX( CASE col_name WHEN 'HSL02' THEN col_value END ) AS hsl02, MAX( CASE col_name WHEN 'HSL03' THEN col_value END ) AS hsl03, MAX( CASE col_name WHEN 'HSL04' THEN col_value END ) AS hsl04, MAX( CASE col_name WHEN 'HSL05' THEN col_value END ) AS hsl05, MAX( CASE col_name WHEN 'HSL06' THEN col_value END ) AS hsl06, MAX( CASE col_name WHEN 'HSL07' THEN col_value END ) AS hsl07, MAX( CASE col_name WHEN 'HSL08' THEN col_value END ) AS hsl08, MAX( CASE col_name WHEN 'HSL09' THEN col_value END ) AS hsl09, MAX( CASE col_name WHEN 'HSL10' THEN col_value END ) AS hsl10, MAX( CASE col_name WHEN 'HSL11' THEN col_value END ) AS hsl11, MAX( CASE col_name WHEN 'HSL12' THEN col_value END ) AS hsl12, MAX( CASE col_name WHEN 'HSL13' THEN col_value END ) AS hsl13, MAX( CASE col_name WHEN 'HSL14' THEN col_value END ) AS hsl14, MAX( CASE col_name WHEN 'HSL15' THEN col_value END ) AS hsl15, MAX( CASE col_name WHEN 'HSL16' THEN col_value END ) AS hsl16 FROM @lt_data_unpivot AS a GROUP BY ryear,racct,rbukrs,drcrk,activ,awtyp INTO TABLE @DATA(lt_data_pivot).
cl_demo_output=>display( lt_data_pivot ).效果如下：
总结
列转行(UNPIVOT)需要借助中间表 Z TSERIES，一行数据有N列需要转换，JOIN Z TSERIES表的时候就让NUM<=N.
行转列(PIVOT)就不需要借助中间表 Z TSERIES，GROUP BY加上CASE WHEN 就搞定 .
