Home » SQL & PL/SQL » SQL & PL/SQL » Query to convert data in single column (Oracle 11g R2)
Query to convert data in single column [message #568424] Thu, 11 October 2012 08:16 Go to next message
srinivas.k2005
Messages: 299
Registered: August 2006
Senior Member
Hi,

I have a below requirement let me know the optimized query.


Create table test_123 ( sr varchar2(1000));

Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');

I need output as :

Sr_output

1
2
3
4
5
6
7
8
9
10
11

I need all the values in the column sr in a single column and mutiple rows



Thanks,
SRK
Re: Query to convert data in single column [message #568426 is a reply to message #568424] Thu, 11 October 2012 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FAQ.
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


Have a look at row generator topic.

Regards
Michel
Re: Query to convert data in single column [message #568427 is a reply to message #568424] Thu, 11 October 2012 08:29 Go to previous message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
SQL> select  distinct to_number(regexp_substr(sr,'[^,]+',1,column_value)) sr_output
  2    from  test_123,
  3          table(
  4                cast(
  5                     multiset(
  6                              select  level
  7                                from  dual
  8                                connect by level <= length(regexp_replace(sr,'[^,]')) + 1
  9                             )
 10                     as sys.OdciNumberList
 11                    )
 12               )
 13    order by sr_output
 14  /

 SR_OUTPUT
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

11 rows selected.

And if you are on 11g:

SQL> select  distinct to_number(regexp_substr(sr,'[^,]+',1,column_value)) sr_output
  2    from  test_123,
  3          table(
  4                cast(
  5                     multiset(
  6                              select  level
  7                                from  dual
  8                                connect by level <= regexp_count(sr,',') + 1
  9                             )
 10                     as sys.OdciNumberList
 11                    )
 12               )
 13    order by sr_output
 14  /

 SR_OUTPUT
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

11 rows selected.

SQL> 


SY.
Previous Topic: how to convert date to specific format?
Next Topic: Spooling every 25 rows in excel
Goto Forum:
  


Current Time: Fri Sep 19 21:55:29 CDT 2014

Total time taken to generate the page: 0.10000 seconds