• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Expressing a One to Many Relationship in a Single Row

 
Ranch Hand
Posts: 153
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.





 
Ranch Hand
Posts: 257
Hibernate Oracle Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.






 
author
Posts: 4356
45
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic