CODEWITHSUNDEEP
sqldb2db2-400
Geoff_S
Geoff_S

Reputation: 5107

DB2/Sql grouping with condition

I have a pretty straightforward sql query that I'm running on DB2 I-series 4 which is just performing some simple joins. The issue is that my materials table has it so that multiple material/color combos can be used on each body of work. Right now I'm getting rows of every combination but I want to consolidate so that if the value of test.materials.sequence is 2 then it creates two additional columns (if there's no sequence 2 for the combination then it would just be 0 and 0 in those columns.

THe query:

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;

Tables:

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

This is my current result:

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

This is the result I would want:

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

See how 1234-6 only has the one material combination, so material2 and mat_color2 would be zeroes.

Is there a way through grouping and row-operations to achieve this?

UPDATE:

In answer to Charles' answer, I've realized some issues in one case with more data. After grouping by material, color, and desc I realized that I was getting the missing records but now ending up with this issue:

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

Upvotes: 0

Views: 752

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

I think Charles first answer is closer. Here is what I got:

SELECT SK.ID SKU
      ,BOD.CODE 
      ,MAT.MATERIAL MATERIAL_1
      ,MAT.MAT_COLOR MATERIAL_2
      ,COALESCE(MAT2.MATERIAL, '0') MATERIAL_2
      ,COALESCE(MAT2.MAT_COLOR, '0') COLOR_2
FROM SKUS SK
 INNER JOIN BODIES BOD ON SK.BODY_ID = BOD.ID
 INNER JOIN CATEGORIES PRC ON PRC.ID = SK.CATEGORY_ID
 INNER JOIN SKUS_TO_MATERIALS STM ON SK.ID = STM.SKU_ID AND STM.SEQUENCE = 1
 INNER JOIN MATERIALS MAT ON STM.MAT_ID = MAT.ID
 LEFT JOIN SKUS_TO_MATERIALS STM2 ON SK.ID = STM2.SKU_ID AND STM2.SEQUENCE = 2
 LEFT JOIN MATERIALS MAT2 ON STM2.MAT_ID = MAT2.ID;

Resulting in:

SKU CODE MATERIAL_1 MATERIAL_2 MATERIAL_2 COLOR_2
6 BodD Fabric Black 0 0
4 BodB Fabric Black Leather Black
3 BodA Fabric Black 0 0
2 BodA Fabric Black Leather Black
1 BodA Fabric Black 0 0
1 BodA Fabric White 0 0
5 BodC Leather Brown 0 0
1 BodA Leather Brown 0 0
1 BodA Leather Black 0 0

View on DB Fiddle

Upvotes: 1

Charles
Charles

Reputation: 23783

"rows to columns" is known as "pivoting" the data.

Db2 for IBM i doesn't have a built method to pivot. However, assuming a distinct set of values, you can hardcode it.

Something like so should do what you want:

Select bod.code, max(mat1.material), max(mat1.mat_color),
       max(mat2.material), max(mat2.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 mat1 on stm.mat_id = mat1.id
           and mat1.sequence = 1
left outer join test.materials mat2 on stm.mat_id = mat2.id
           and mat2.sequence = 2
group by bod.code
order by prc.desc;

EDIT Ok if sequence is actually in the skus_to_materials then you'll need a different set of joins. Actually in that case, I'd probably just go with a CTE based statement.

with allrows as (
  Select bod.code, prc.desc, stm.sequence
         , 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
}
select 
  code
  , max(case when sequence = 1 then material else '0' end) as material1
  , max(case when sequence = 1 then color else '0' end) as color1
  , max(case when sequence = 2 then material else '0' end) as material2
  , max(case when sequence = 2 then color else '0' end) as color2
from allRows
group by code
order by desc;

Upvotes: 1

Related Questions