Home » SQL & PL/SQL » SQL & PL/SQL » Row to Column conversion
Row to Column conversion [message #269720] Mon, 24 September 2007 05:14 Go to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Hi
I am looking for sql query which can convert comma seperated string into number of column value.
for eg my query
SELECT 'ONE,TWO,THREE' FROM DUAL ; 

should result:
ONE | TWO | THREE

I hope above eg clear what result i am looking for if anything missing or not clear please write.
String can have multiple value comma separated so i am looking for generic soluton.

Thanks
Pravin




Re: Row to Column conversion [message #269731 is a reply to message #269720] Mon, 24 September 2007 05:48 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select translate('ONE,TWO,THREE',',','|') from dual;
Re: Row to Column conversion [message #269732 is a reply to message #269731] Mon, 24 September 2007 05:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can't, not in a single statement. Oracle needs to know what columns you are selecting.

MHE
Re: Row to Column conversion [message #269736 is a reply to message #269732] Mon, 24 September 2007 05:58 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Thanks for your reply Maaher.

Thanks,
Pravin
Re: Row to Column conversion [message #269802 is a reply to message #269720] Mon, 24 September 2007 12:50 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Check out the following oracle utility

DBMS_UTILITY.COMMA_TO_TABLE
Re: Row to Column conversion [message #269805 is a reply to message #269802] Mon, 24 September 2007 13:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ouch. I don't think DBMS_UTILITY was meant for that. Don't be surprised if it doesn't work out the way you intended.

MHE
Re: Row to Column conversion [message #269806 is a reply to message #269720] Mon, 24 September 2007 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as ( SELECT ','||'ONE,TWO,THREE'||',' data FROM DUAL ),
  3    lines as ( 
  4      select level line, 
  5             substr(data,
  6                    instr(data,',',1,level)+1,
  7                    instr(data,',',1,level+1)-instr(data,',',1,level)-1
  8                   ) val
  9      from data
 10      connect by level < length(data)-length(replace(data,',',''))
 11    )
 12  select max(decode(line,1,val)) col1,
 13         max(decode(line,2,val)) col2,
 14         max(decode(line,3,val)) col3
 15  from lines
 16  /
COL1            COL2            COL3
--------------- --------------- ---------------
ONE             TWO             THREE

1 row selected.

Regards
Michel
Re: Row to Column conversion [message #269893 is a reply to message #269806] Tue, 25 September 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Right Michel, but what if the number of string elements isn't fixed? Just curious if someone can come up with a scalable solution.

MHE
Re: Row to Column conversion [message #269904 is a reply to message #269893] Tue, 25 September 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no scalable solution in SQL.
There is a solution with any number of columns. If you have time you can search on my posts on AskTom, I post some solutions a year or two (or maybe more) there.
Yesterday my PC motherboard crashed leading to a disk failure. I'm currently rebuilding this but I no more have Oracle on my PC just the more important thing: connection to OraFAQ. Wink

Regards
Michel

Re: Row to Column conversion [message #269906 is a reply to message #269904] Tue, 25 September 2007 01:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Tue, 25 September 2007 08:48
I'm currently rebuilding this but I no more have Oracle on my PC...
That's a lame excuse Very Happy. All you need is a workspace at apex.oracle.com. Free, and always there...

MHE
Re: Row to Column conversion [message #270021 is a reply to message #269720] Tue, 25 September 2007 07:54 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Interesting enough, The new oracle 11g has pivot commands built into select. See the following link.


http://www.psoug.org/reference/ora_operators.html#oppv

Re: Row to Column conversion [message #270023 is a reply to message #270021] Tue, 25 September 2007 08:03 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So Bill, as of 11g there is a scalable solution? Can anyone verify? This is interesting.

MHE

[Updated on: Tue, 25 September 2007 08:04]

Report message to a moderator

Previous Topic: default behaviour of timestamp
Next Topic: Cursor? Please guide!
Goto Forum:
  


Current Time: Fri Dec 02 14:34:54 CST 2016

Total time taken to generate the page: 0.41664 seconds