Column to Rows

[himanshu990]

Hey,
I M stuck with a problem. Here it is as :
I have a table called Temp which has somem columns.Now one column hav differnt value but comma separated.For ex:
Temp Name TempLocation
test abc,xyz,lmn

Now I need to resolve this one record into multiple records based on differnt
Values in "TempLocation"
So that I should get 3 records with same TampName but Differnt TempLocation.
Please tell me how can I do it ???

[gandolf989]

This is an example of a pivot query using analytics.

SELECT table_name, columns
  FROM ( SELECT table_name, 
                LTRIM(columns, ', ') columns
           FROM ( SELECT table_name,
                         MAX(sys_connect_by_path(
                             column_name, ', ')) columns
                    FROM ( SELECT table_name, column_name,
                                  row_number() OVER
                                ( PARTITION BY table_name
                                      ORDER BY table_name ) row_num
                             FROM user_tab_columns)
                   START WITH row_num = 1
                 CONNECT BY PRIOR row_num   = row_num - 1
                     AND PRIOR table_name = table_name
                   GROUP BY table_name))
 ORDER BY table_name;

[himanshu990]

Hey Man,,
FOF Thanks..that u gave me such anser.However My worry is ..if I need to do it through a for loop can u give me some suggestion....I am tryin it ..though not been able to make it fully..

[gandolf989]

I'm not sure why you would need a loop. You have data that needs to be aggregated to one field joined with parent data. Using a query like I mentioned should meant that you don't need a loop. Unless I am missing something.

[himanshu990]

Query that u hav sent seem prtty complex to me..it may be bcoz im not a plsql developer..i use plsql very often..
But Now wen u hav already told me abt this query ..i tried it and put the required values to it.still its nt working...i changed the table_name given by u and put there my table name.Nt working..

[gandolf989]

Query that u hav sent seem prtty complex to me..it may be bcoz im not a plsql developer..i use plsql very often..
But Now wen u hav already told me abt this query ..i tried it and put the required values to it.still its nt working...i changed the table_name given by u and put there my table name.Nt working..

Can you post the query and results/error message exactly as it appears?