Home » SQL & PL/SQL » Client Tools » Splitting a column into multiple columns (Oracle 11G)
Splitting a column into multiple columns [message #562361] Tue, 31 July 2012 13:57 Go to next message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Hi Guys,

I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).

Quote:
FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE

RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE

Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION

RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION



Your help is much appreciated.

Thanks in advance,
shil
Re: Splitting a column into multiple columns [message #562363 is a reply to message #562361] Tue, 31 July 2012 14:15 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

having comma separated string in single column violates data normalization rules.
Re: Splitting a column into multiple columns [message #562364 is a reply to message #562361] Tue, 31 July 2012 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ, please search before posting.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Here's an example:
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select substr(:mylist,
  2                instr(','||:mylist||',', ',', 1, rn),
  3                instr(','||:mylist||',', ',', 1, rn+1)
  4                - instr(','||:mylist||',', ',', 1, rn) - 1) value
  5  from ( select rownum rn from dual 
  6         connect by level 
  7                      <= length(:mylist)-length(replace(:mylist,',',''))+1
  8       )
  9  /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45

This use a row generator.

Regards
Michel
Re: Splitting a column into multiple columns [message #565261 is a reply to message #562364] Sun, 02 September 2012 07:23 Go to previous message
rvijayaragavan86
Messages: 6
Registered: September 2012
Location: Bangalore
Junior Member
You could try using dbms_utility.comma_to_table procedure
Previous Topic: Query stored Unicode URDU Data through SQL*PLUS
Next Topic: Comment on Column with & symbol
Goto Forum:
  


Current Time: Sun Dec 21 05:52:53 CST 2014

Total time taken to generate the page: 0.12214 seconds