Questions :
Employee table: A (e_id,name,) Department table: B (d_id,d_name) employee-to-Department relationship: C (id,e_id,d_id)
SELECT A.e_id,a.name, b.d_name from A INNER joins C on a.e_id=c.e_id INNER joins B on b.d_id=c.d_id;
The record of such investigation will appear,
1, Zhang San, research and Development department
1, Zhang San, marketing department
2, John Doe, sales Department
I think the query results are shown in this way:
1, Zhang San, research Department, marketing department 2, John Doe, Sales Department 3 ...
How does SQL write?
Solution:
Method 1
SELECT a.e_id, A.name, REPLACE (Wm_concat (b.d_name), ', ', ' | ') D_name from A INNER join C on a.e_id = c.e_id INNER join B on b.d_id = c.d_id GROUP by a.e_id, A.N Ame
Method 2:
select a.e_id, A.name, Listagg (B.d_name, " ) within group (order by B.d_name) D_name from A inner Span style= "color: #808080;" >join C on a.e_id = c.e_id inner join B on b.d_id c.d_id group by a.e_id, A.name
Application of Oracle row and column transformation