Printable View
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 ???
This is an example of a pivot query using analytics.
Code: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;
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..
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.
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?Quote:
Originally Posted by himanshu990