DB2 SQL列转行实现需求及现有SQL方案咨询

嗨，针对你在DB2里要实现的列转行需求，我给你整理了几种实用的方案，比你目前用的CASE写法更简洁高效哦：

需求回顾 #

原表数据：

目标转换后的数据：

方案1：使用UNION ALL（兼容所有DB2版本） #

这是最通用的写法，不管你的DB2版本新老都能跑，逻辑也直白得很——就是把每一列拆成单独的查询，再把结果合并起来就行。

如果你的原数据是通过CTE定义的（就像你写的with t as (...)），直接套这个模板就行：

WITH t AS (
    -- 这里放你的自定义查询，比如原数据的获取逻辑
    SELECT 1 AS ROW, 12 AS Column1, 25 AS Column2, 11 AS Column3 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT 2 AS ROW, 30 AS Column1, 5 AS Column2, 15 AS Column3 FROM SYSIBM.SYSDUMMY1
)
SELECT 
    t.ROW,
    1 AS Columns,
    t.Column1 AS values_
FROM t
UNION ALL
SELECT 
    t.ROW,
    2 AS Columns,
    t.Column2 AS values_
FROM t
UNION ALL
SELECT 
    t.ROW,
    3 AS Columns,
    t.Column3 AS values_
FROM t
ORDER BY t.ROW, Columns;

要是直接操作物理表，把CTE部分换成你的表名即可，非常灵活。

方案2：使用DB2的TABLE函数+JSON/XML（适合DB2 11.1+版本） #

如果你的DB2版本比较新（11.1及以上），可以用更简洁的写法，借助JSON或者XML批量拆解列，避免重复写一堆UNION ALL：

基于JSON的写法 #

WITH t AS (
    -- 你的自定义查询
    SELECT 1 AS ROW, 12 AS Column1, 25 AS Column2, 11 AS Column3 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT 2 AS ROW, 30 AS Column1, 5 AS Column2, 15 AS Column3 FROM SYSIBM.SYSDUMMY1
)
SELECT 
    t.ROW,
    JSON_VALUE(item, '$.key') AS Columns,
    JSON_VALUE(item, '$.value') AS values_
FROM t,
     TABLE(
         JSON_TABLE(
             JSON_OBJECT(
                 '1' VALUE Column1,
                 '2' VALUE Column2,
                 '3' VALUE Column3
             ),
             '$.*' COLUMNS(
                 item VARCHAR(100) FOR BIT DATA PATH '$'
             )
         )
     ) AS unpivoted
ORDER BY t.ROW, Columns;

基于XML的写法 #

WITH t AS (
    -- 你的自定义查询
    SELECT 1 AS ROW, 12 AS Column1, 25 AS Column2, 11 AS Column3 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT 2 AS ROW, 30 AS Column1, 5 AS Column2, 15 AS Column3 FROM SYSIBM.SYSDUMMY1
)
SELECT 
    t.ROW,
    CAST(xmlcolumn AS INT) AS Columns,
    CAST(xmlvalue AS INT) AS values_
FROM t,
     XMLTABLE(
         'for $i in (1,2,3) return <row><col>{$i}</col><val>{fn:column("Column"||$i)}</val></row>'
         COLUMNS
             xmlcolumn VARCHAR(10) PATH 'col',
             xmlvalue VARCHAR(10) PATH 'val'
     ) AS x
ORDER BY t.ROW, Columns;

方案对比 #

• UNION ALL方案：兼容性拉满，逻辑简单好懂，适合列数不多的场景；缺点是列数多的时候要写不少重复代码。
• JSON/XML方案：代码更紧凑，列数多的时候维护更省心，但对DB2版本有要求，性能上和UNION ALL差不多（取决于数据量）。

你原来用的CASE+数字表的写法其实也能实现，但需要先构建一个数字序列表（比如n=1,2,3），还得关联查询，相比上面的方案没什么明显优势，反而多了维护数字表的麻烦，所以更推荐上面两种方案哦。

内容的提问来源于stack exchange，提问作者hkn