DB2 iSeries 4中如何通过分组行操作实现物料组合列转行？

问题背景 #

在DB2 iSeries 4上执行关联SQL查询时，当前会返回每个物料/颜色组合的行数据，需求是按test.skus_to_materials表的sequence字段合并数据：当存在sequence=2的记录时新增对应列，无sequence=2的记录则填充0。

当前查询语句 #

Select bod.code, mat.material, mat.mat_color
from test.skus sk
inner join test.Bodies bod on sk.body_id = bod.id
inner join test.categories prc on prc.id = sk.category_id
inner join test.skus_to_materials stm on sk.id = stm.sku_id
inner join test.materials mat on stm.mat_id = mat.id
order by prc.desc;

表结构及数据 #

skus表 #

id  |  code  |  body_id  |  category_id  
------------------------------------------- 
1      12345     9912            3
2      12346     9913            3

Bodies表 #

id   |  code 
--------------------------
9912    1234-5
9913    1234-6

categories表 #

id  |  category
------------------
3     Test

skus_to_materials表 #

id  |  sku_id  |  mat_id  |  sequence
--------------------------------------
1     1           221         1
2     1           222         2
3     2           223         1

materials表 #

id  |  material  |  mat_color 
-------------------------------
221    Fabric       black     
222    Fabric       white     
223    Leather      brown     

当前查询结果 #

code  | material  | mat_color
-------------------------
1234-5 | Fabric    | black
1234-5 | Fabric    | white

期望结果 #

code | material1 | mat_color1 | material2 | mat_color2
----------------------------------------------------------
1234-5    Fabric        black      Fabric     white
1234-6    Leather       brown       0           0

分组后遇到的问题 #

尝试通过分组操作实现时，出现数据分散的问题，结果如下：

code   |   material1   |   color1   |  material2  |  color2
------------------------------------------------------------
1234-5      Fabric         White         0               0
1234-5      0               0          Leather         white 
1234-5      Leather         Brown        0               0
1234-5      Leather         Tan          0               0
1234-6      Fabric         Black         0               0 
1234-6      0              0           Leather         Black 
1234-7     Fabric         White          0               0

解决方案 #

可以通过条件聚合+分组实现需求，核心是按bod.code分组，利用CASE WHEN配合聚合函数（如MAX）将不同sequence对应的物料、颜色聚合到同一行，无对应数据则填充0。具体SQL语句如下：

SELECT 
    bod.code,
    MAX(CASE WHEN stm.sequence = 1 THEN mat.material ELSE '0' END) AS material1,
    MAX(CASE WHEN stm.sequence = 1 THEN mat.mat_color ELSE '0' END) AS mat_color1,
    MAX(CASE WHEN stm.sequence = 2 THEN mat.material ELSE '0' END) AS material2,
    MAX(CASE WHEN stm.sequence = 2 THEN mat.mat_color ELSE '0' END) AS mat_color2
FROM test.skus sk
INNER JOIN test.Bodies bod ON sk.body_id = bod.id
INNER JOIN test.categories prc ON prc.id = sk.category_id
LEFT JOIN test.skus_to_materials stm ON sk.id = stm.sku_id
LEFT JOIN test.materials mat ON stm.mat_id = mat.id
GROUP BY bod.code
ORDER BY prc.desc;

关键说明 #

1. 将INNER JOIN改为LEFT JOIN关联skus_to_materials和materials，确保即使无对应物料记录的sku也能被保留并填充0。
2. 通过CASE WHEN判断sequence值，分别提取对应material和mat_color，无对应值时返回'0'。
3. 用MAX聚合函数确保每个code分组下仅保留非0的有效数据，避免出现多行分散的问题。
4. 最终按bod.code分组，确保每个code仅返回一行结果，符合期望格式。

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