Previous Discussion	Next Discussion

Comma separated data in a field to rows... Ok...here is the setup. We have a database that has a field with comma-separated values. Due to the nature of the project I am working on, I need to be able to convert the comma separated values into rows, so that I can bind them to a drop-down list. So the table might be like this: FIELD1 ------ A, B, C, D, E But what I want is: FIELD1 ------ A B C D E The database we're using is in Oracle. I'd rather do it without a stored procedure or a function, if that's at all possible. Any thoughts or help on this would be greatly appreciated. Started By Monte on Jul 23, 2010 at 3:15:08 PM

6 Response(s) | Reply

Earlier Replies | Replies 4 to 6 of 6 | Later Replies

Monte on Jul 26, 2010 at 2:13:53 PM (# 4) I've figured this out. I have no idea if this works with SQL Server. It does, however, work with Oracle 10g. Here's the solution: SELECT field1,     SUBSTR(field2, INSTR(field2, ',', 1, LEVEL) + 1, INSTR(field2, ',', 1, LEVEL+1) - INSTR(field2, ',', 1, LEVEL) - 1) NAME   FROM     (SELECT ',' || field2 ||',' AS field2, field1 FROM my_table )     CONNECT BY LEVEL  <= 100   AND INSTR(field2, ',', 1, LEVEL+1)  > 0   AND PRIOR DBMS_RANDOM.STRING('p', 10) IS NOT NULL In case anyone runs into this problem in the future. It's probably not the most elegant solution, and I'm not exactly sure how it works, I just know it does... bod1467 on Aug 2, 2010 at 8:34:18 AM (# 5) How many rows would be returned by a simple query? i.e. how many records are there in the original table containing data like A,B,C,D,E? If there's only one then why not just return the one row and then split (explode) the result on comma into an array, trim each element (if necessary), then populate into the droplist? (Even if there are several records in the database then this would still work fine). Or is there significantly more to this question than suggested by the basic overview? :-) Monte on Aug 2, 2010 at 11:26:56 AM (# 6) To answer your question, there are several rows. I don't know for sure exactly how many, but it's definitely more than 1. I had actually thought about what you suggested, but there's more than one row with this type of data in it, even if I use clauses to narrow down the results.

Earlier Replies | Replies 4 to 6 of 6 | Later Replies

To respond to a discussion, you must first logon.

If you are not registered, please register yourself to become a member of the SiteExperts.community.