CODEWITHSUNDEEP
oracle-databaseleft-joinpivot
user1523257
user1523257

Reputation: 61

Oracle - combining multiple rows to columns via left join

I'm working on a mini project to learn some SQL. I am currently having some difficulty with combining multiple rows into columns.

The table I am trying to clean is 

GroupID Product   Attribute1   Attribute 2        
100      1           blue          red
100      2           gold          blue
101      1           pink          blue
101      2           black         white
101      3           purple        grey

What I'm trying to produce is a table with only unique groupID's, so:

Group ID    Product1  pr1Attribute 1   pr1Attribute2    Product2  pr2Attribute1  pr2Attribute2 Product3  pr3Attribute1  pr3Attribute2

row data:

100 1 blue red 2 gold blue null null null
101 1 pink blue 2 black white 3 purple grey

I've tried the following 

select distinct full.groupid, product1.product as product1,
    product1.attr1 as product1_attrib1, product1.attr2 as product1_attrib2,
    product2.product as product2, product2.attr1 product2_attribute1, 
    product2.attr2 product2_attribute2, product3.product as product3, 
    product3.attr1 as product3_attrib1, product3.attr2 as product1_attrib2
from tabletest full
left join tabletest product1 on (full.groupid = product1.groupid and product1.producttem = '1')
left join tabletest product2 on (full.groupid = product1.groupid and product2.producttem = '2')
left join tabletest product3 on (full.groupid = product1.groupid and product3.producttem = '3');

The query does not work correctly. It seems to not take into account the AND or duplicate in some way.

Any guidance would be greatly appreciated. I was also wondering if there was a way of implementing this such that I did not "know" any of the product numbers (so basically I wouldn't have to hard code them in).

Upvotes: 1

Views: 1581

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

I think you have a simple copy/paste bug: join conditions for tables product2 and product3 errorneously reference product1's id in the first part of their join criteria.

left join tabletest product1 on (full.groupid = product1.groupid and product1.producttem = '1')
left join tabletest product2 on (full.groupid = product2.groupid and product2.producttem = '2')
left join tabletest product3 on (full.groupid = product3.groupid and product3.producttem = '3');

Upvotes: 2

Related Questions