Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Transform Columns to Rows (Unusual)

Transform Columns to Rows (Unusual)

From: David Berg <dbergquist_at_bd.com>
Date: 5 Feb 2003 11:43:25 -0800
Message-ID: <1274998b.0302051143.40dfb5bd@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US