Need help with a query 1

[JazzMaan]

have a po_item table and po_item_details table with one to many relationship. I need to display multiple records in po_item_details in a flat format.

select pi.po_item_id, pid.qualifier, descrption
from po_items pi left join po_item_identifiers pid
on (pi.PO_ITEM_ID = pid.po_Item_ID)

gives:

1, style, fall
1, color, red
1, sku, 111
2, style, fall
2, color, blue
2, sku, 112

I need to show it as:
1, fall, red, 111
2, fall, blue, 112

I am new to Oracle. In sql server i could do this using a temp table.

Thanks

 

[SantaMufasa]

Actually, JazzMaan, your original code gives these results:

select pi.po_item_id, pid.qualifier, descrption
from po_items pi left join po_item_identifiers pid
on (pi.PO_ITEM_ID = pid.po_Item_ID)
/
        1 style fall
        1 color red
        1 sku   111
        2 style fall
        2 color blue
        2 sku   112

Notice fixed-width columns and no commas. Also, your data are not indicative of missing child rows that would exercise the left-join functionality of your code.

Therefore, I've addes some data to both tables to illustrate both the left-join functionality and to show a syntactical alternative in Oracle, "(+)", that also does left joining. My code, below, also achieves the "flat-file" functionality of variable-width columns separated by commas. (My code presumes that you are interested in no more details than style, color, and SKU.):

Section 1 -- Sample data:

SQL> select * from po_items;
         1
         2
         3
         4

4 rows selected.

SQL> select * from po_item_identifiers;
         1 style fall
         1 color red
         1 sku   111
         2 style fall
         2 color blue
         2 sku   112
         4 color grey
         4 sku   113

8 rows selected.

Section 2 -- Code to achieve variable-width, flat-file results:

set pagesize 0
select pi.po_item_id
       ||','||style.descrption
       ||','||color.descrption
       ||','||sku.descrption
from po_items pi
    ,po_item_identifiers style
    ,po_item_identifiers color
    ,po_item_identifiers sku
where pi.po_item_id = style.po_item_id(+) and 'style' = style.qualifier(+)
  and pi.po_item_id = color.po_item_id(+) and 'color' = color.qualifier(+)
  and pi.po_item_id = sku.po_item_id(+)   and 'sku'   = sku.qualifier(+)
/
1,fall,red,111
2,fall,blue,112
3,,,
4,,grey,113

4 rows selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[JazzMaan]

Thanks so much Mufasa... u are the man