Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Transform Columns to Rows (Unusual)
Have a problem similar to the typical transforming columns into row
that i have seen posted here, but with some unique twists that making
it a problem for me.
Need query that transforms an initial dataset and adds a new row for each string initially stored as a comma delimited array in varchar field rather than a column for each entry to transform. Return 1 row if 0 or 1 string in array field.
Source: Table1
ID ProductName OldType
1 Everclear textstring1 2 Doritos textstring1,textstring2, 3 Snickers textstring1,textstring2....textstring_N, 4 Dr Pepper (null)
-ID is row PriKey
-ProductName is field in table.
-OldType is varchar array field to transform and create new record set
based on. It contains comma delimited text of 0 to 10 strings (last
entry will be marked with comma if there is one or more string in
list)
Output: View1 or StoredProc1
ID ProductName ExpandedType
1 Everclear textstring1 2 Doritos textstring1 2 Doritos textstring2 3 Snickers textstring1 3 Snickers textstring2 ... ... 3 Snickers textstringN 4 Dr. Pepper (null)
Thanks for your help. Received on Wed Feb 05 2003 - 13:43:25 CST