CODEWITHSUNDEEP
sqldb2pivotaggregate-functionswindow-functions
Abhishek Parashar
Abhishek Parashar

Reputation: 1

How to pivot row data into specific columns db2

I would like to pivot results from a table into a new structure. So that it can map all the children to the parent product.

Current Result

Parent_Prod_Num|Child_Prod_Num|Child_Prod_Code|Child_Prod_Name
1|11|a123|a
1|12|b123|ab
1|13|c123|abc

Expected Result

Parent_Prod_Num|Child_Prod_Num_1| Child_Prod_Code_1|Child_Prod_Name_1| Child_Prod_Num_2| Child_Prod_Code_2|Child_Prod_Name_2| Child_Prod_Num_3| Child_Prod_Code_3|Child_Prod_Name_3
1|11|a123|a|12|b123|ab|13|c123|abc

Upvotes: 0

Views: 125

Answers (1)

GMB
GMB

Reputation: 222442

For a fixed maximum number of children per parent, you can enumerate the rows with row_number(), then pivot with conditional aggregation:

select parent_prod_num,
    max(case when rn = 1 then child_prod_num  end) as child_prod_num_1,
    max(case when rn = 1 then child_prod_code end) as child_prod_code_1,
    max(case when rn = 1 then child_prod_name end) as child_prod_name_1,
    max(case when rn = 2 then child_prod_num  end) as child_prod_num_2,
    max(case when rn = 2 then child_prod_code end) as child_prod_code_2,
    max(case when rn = 2 then child_prod_name end) as child_prod_name_2,
    max(case when rn = 3 then child_prod_num  end) as child_prod_num_3,
    max(case when rn = 3 then child_prod_code end) as child_prod_code_3,
    max(case when rn = 3 then child_prod_name end) as child_prod_name_3
from (
    select t.*, row_number() over(partition by parent_prod_num order by child_prod_num) rn
    from mytable t
) t
group by parent_prod_num

Upvotes: 1

Related Questions