File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes  Expressing a One to Many Relationship in a Single Row Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark " Expressing a One to Many Relationship in a Single Row" Watch " Expressing a One to Many Relationship in a Single Row" New topic
Author

Expressing a One to Many Relationship in a Single Row

ludoviko azuaje
Ranch Hand

Joined: Jun 07, 2005
Posts: 53
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.






SCJP 1.4, SCJP 5.0, SCWCD 1.4, Oracle SQL Expert Exam 1Z0-047
IBM Test 255, IBM Test 730, IBM Test 142, ScrumManager® Certified.
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 255

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.







Regards
Salil Verma
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Expressing a One to Many Relationship in a Single Row