sql - 如何在oracle中将特定id的不同值放入单行？(How to get different value of particular id into single row in oracle?)

Question

I had an below table

(我有一张桌子)

y TABLE

(表)

id     val
--     ---
1       A 
1       B 
1       C 
2       B
2       C
3       A

Need output like below

(需要如下输出)

 sno   val1    va2       val3
-----  ----   -----     ------
  1      A     B          C   
  2      B     C          -
  3      A     -          -

I wrote query with self join like

(我写了像自我加入查询)

SELECT t1.id as sno, t1.val as val1, t2.val as va2, t3.val as val3
  FROM y t1 JOIN y t2 ON t1.id = t2.id
  JOIN y t3 ON t2.id = t3.id
  ORDER BY 1,2,3,4;

But i returns like Cartesian join.

(但是我回来就像笛卡尔加入。)

How to get proper result.

(如何获得适当的结果。)

  ask by Naveen M translate from so

Answer 1

You can use row_number() and conditional aggregation:

(您可以使用row_number()和条件聚合：)

select id,
       max(case when seqnum = 1 then val end) as val_1,
       max(case when seqnum = 2 then val end) as val_2,
       max(case when seqnum = 3 then val end) as val_3
from (select t.*
             row_number() over (partition by id order by val) as seqnum
      from t
     ) t
group by id;