• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Expressing a One to Many Relationship in a Single Row

 
ludoviko azuaje
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hy,

is there a way to Express a One to Many Relationship in a Single Row using Sql-Oracle, in those cases where the returned data is repeated except for one column. Then it would be easier to return the distinct values from that column into a one single row with those values separated by commas.

I appreciate any help.

thanks.





 
Salil Vverma
Ranch Hand
Posts: 257
Hibernate Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ludoviko,
As per my understanding of you have the data like

EmpID DeptID
101 10
102 10
103 10
104 20
105 20
106 30
107 20

and want the out put like -

DeptID Emps
10 101,102,103
20 104,105,107
30 106

If my understanding about your question is correct, then it is more a sql query related question than a jdbc related query.

In oracle you are use the following query to get the required result -




I hope this would resolve your query.






 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What you're looking for is the GROUP_CONCAT() function which con combine a number of related rows into a single column with values separated by commas and/or whatever deliminator you need. Keep in mind, the name of the function in MySQL is GROUP_CONCAT, but its name likely changes between database systems as it is a non-standard command.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic