Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle sql

 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to generate the following result by using oracle Sql

Illinois chicago,Desplaines,Mt.Prospect

I tried all the ways but i am not getting the required result.

state_id state_name
---------------------


1 Illinois



city_id state_id city_name
---------------------------
1 1 chicago
2 1 Desplaines
3 1 Mt.Prospect



regards
sudha
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sudha,
Why not do a join and create the comma separated value list in Java? Since you posted in the JDBC forum, I assume you are using Java.

If not, you could use an Oracle specific function. Pivot() was introduced recently to help with this. I'm sure there are other similar functions that accomplish this. If you really need something Oracle specific, I recommend posting in our Oracle forum. (or letting me know so I can move this thread.)
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

select state_id,city_id,city_name from state a, city b where a.state_id=b.state_id.
I will be getting 3 records for the state id 1.I am not clear how to do it in java.
Any help is appreciated on this.

regards
sudha
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sudha,
Let's start by separating it into smaller parts.

1) Can you write a loop that stores all of the distinct state_id's from that result set and stores them in whatever data structure you are using?
2) Can you add the first name for each state_id in that data structure?
3) Can you add a comma and all subsequent names in that data structure?

Post how far you get and we can offer more concrete advice.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic