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
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 |