Expressing a One to Many Relationship in a Single Row

[ludoviko azuaje]

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]

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 -

 
select deptno,
max(sys_connect_by_path(empid1, ' ' )) empids
from (select deptno, empid||',' empid1,
row_number() over
(partition by deptno order by ename) rn
from emp
)
start with rn = 1
connect by prior rn = rn-1 and prior deptno = deptno
group by deptno

I hope this would resolve your query.

[Scott Selikoff]

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.