Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to reverse
SQL query to reverse [message #245226] Fri, 15 June 2007 10:17 Go to next message
srinivas.k2005
Messages: 340
Registered: August 2006
Senior Member
Hi,

I have a requirement.

I have a table locations with column city and some values.

Locations

City

Beijing
Bern
Bombay
Geneva
Hiroshima


When i give the below query:

select MAX(SUBSTR(SYS_CONNECT_BY_PATH(city,','),2)) cities
from ( select city
, rownum rn
from locations
)
connect by prior rn = rn-1
start with rn = 1

I will get the output as :

CITIES

Beijing,Bern,Bombay,Geneva,Hiroshima

Now i want the reverse:

Like :

CITIES

Beijing,Bern,Bombay,Geneva,Hiroshima

To:

City

Beijing
Bern
Bombay
Geneva
Hiroshima

Can you help me in writing the query.

Thanks,
Srinivas

[Updated on: Fri, 15 June 2007 10:18]

Report message to a moderator

Re: SQL query to reverse [message #245237 is a reply to message #245226] Fri, 15 June 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't know the rules:
Always post your Oracle version (4 decimals).
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel
Re: SQL query to reverse [message #245540 is a reply to message #245237] Mon, 18 June 2007 01:23 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Apart from Michel's reply, this one's been covered many times before. The keywords to search for are: str2tbl, string2tab or variants.

MHE
Previous Topic: Time condtion using Oracle
Next Topic: Help with keys
Goto Forum:
  


Current Time: Wed Dec 07 16:14:44 CST 2016

Total time taken to generate the page: 0.23825 seconds