Query - Comma list to rows

[mdiaz1328]

I cannot seem to find the solution to this, your help would be veru much appriciated.

I need to create a query that takes a column containg a comma delimited list and produces rows for each value...

For example.

Table A

Item_Name Item_Number Item_List
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Item1 1000 “W,X,Y,Z”
Item2 2000 “W,X,Y,Z”
Item3 3000 “W,X,Y,Z”




Here is what I would like for results:

Item_Name Item_Number Item_List Item_Value
Item1 1000 “W,X,Y,Z” W
Item1 1000 “W,X,Y,Z” X
Item1 1000 “W,X,Y,Z” Y
Item1 1000 “W,X,Y,Z” Z
Item2 2000 “W,X,Y,Z” W
Item2 2000 “W,X,Y,Z” X
Item2 2000 “W,X,Y,Z” Y
Item2 2000 “W,X,Y,Z” Z
Item3 3000 “W,X,Y,Z” W
Item3 3000 “W,X,Y,Z” X
Item3 3000 “W,X,Y,Z” Y
Item3 3000 “W,X,Y,Z” Z

How can I accomplish this ?

Thanx again !

 

[SantaMufasa]

MDiaz,

Are there a fixed number of items in your ITEM_LIST, or is the number variable?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[mdiaz1328]

Sorry, I probably should have mentioned that. The length (number of values) of the list is NOT fixed.

-Mario-