INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...(I) have been able to get my problems solved from past messages and also new posts that other users have responded to promptly..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release 11g Forum

Query - Comma list to rows

Read More Threads Like This One

mdiaz1328 (TechnicalUser)

13 Apr 12 17:15

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 (TechnicalUser)

13 Apr 12 19:51

MDiaz,

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

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

mdiaz1328 (TechnicalUser)

13 Apr 12 21:45

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

-Mario-

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release 11g