Application of Oracle row and column transformation

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.