Re: Transform Columns to Rows (Unusual)

Probably not possible in straight SQL but a doddle in PL/SQL. Unfortunately, havent got time (or SQL*Plus) to produce full code for you! :-(

M

dbergquist_at_bd.com (David Berg) wrote in message news:<1274998b.0302051143.40dfb5bd_at_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 Thu Feb 06 2003 - 03:03:07 CST