Re: Transform Columns to Rows (Unusual)

David Berg wrote:

> 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.

This can not be done in a SQL statement like a pivot with DECODE. This will require writing a stored procedure.

Take a whack at it and if you have problems post your code and we'll take a look.

Also please do not cross-post. You'll get no better answers and you will anger a lot of the very people you hope will help you. Accordingly I have dropped c.d.o from my response.

Daniel Morgan Received on Wed Feb 05 2003 - 18:33:45 CST