Query On One data point per records

[pkohli8]

Hi,
I have table in this form SAY TABLE

upload_id col_name Value Row
13668 SS_ID 112 1
13668 AK DLP-2 1
13676 SS_ID 113 4
13676 AJ HP 4
13676 AK DSP-1 4
14576 SS_ID 142 3
14676 AI SP 3
14676 AJ Dell 3

would like to tranform this table to below table

upload_id SS_ID AK AI AJ Row
13668 112 DLP-2 -- -- 1
13676 113 DSP-2 -- HP 4
13676 142 -- SP Dell 3

right now I am using below code , but using i am not able to figure out where is the problem..

can you please suggest something

Select
tl.upload_id,
ss.value,
ak.value,
AI.value,
AJ.value,

from
TABLE tl
LEFT JOIN table ss ON tl.upload_id = ss.upload_id AND tl.col_name = 'SS_ID' AND ss.col_name = 'SS_ID' AND tl.row_id = ss.row_id
LEFT JOIN table AK ON tl.upload_id = AK.upload_id AND tl.col_name = 'SS_ID' AND AK.col_name = 'AK' AND tl.row_id = AK.row_id
LEFT JOIN table AI ON tl.upload_id = AI.upload_id AND tl.col_name = 'SS_ID' AND AI.col_name = 'AI' AND tl.row_id = AI.row_id
LEFT JOIN table AJ ON tl.upload_id = AJ.upload_id AND tl.col_name = 'SS_ID' AND AJ.col_name = 'AJ' AND tl.row_id = AJ.row_id


Thanks

 

[SantaMufasa]

First, pkohli8, you did not reconcile your input data with your output results...your data are goofy. Once you fix your input data, here is code that meets your need:

select ss.upload_id, ss.value ss_id, ak.value ak, ai.value ai, aj.value aj, ss.row_num
   from (select * from say where col_name = 'SS_ID') ss
       ,(select * from say where col_name = 'AK') ak
       ,(select * from say where col_name = 'AI') ai
       ,(select * from say where col_name = 'AJ') aJ
 where ss.upload_id=ak.upload_id(+) and ss.row_num = ak.row_num(+)
   and ss.upload_id=ai.upload_id(+) and ss.row_num = ai.row_num(+)
   and ss.upload_id=aj.upload_id(+) and ss.row_num = aj.row_num(+)
/

UPLOAD_ID SS_ID      AK         AI         AJ            ROW_NUM
--------- ---------- ---------- ---------- ---------- ----------
    13668 112        DLP-2                                     1
    13676 113        DSP-1                 HP                  4
    14676 142                   SP         Dell                3

As you can see, I prefer the brevity of Oracle's "(+)" outer join notation versus the "LEFT JOIN..." syntax.

If you have questions, please let us know.

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]