如何在SQL oracle中将一个列值转换为行标题，并将另一个列的值作为其值

Question

我在表中有这样的数据

ID  NAME    TYP  VALUE
== ======  ====== =======
1   JON      B    123
1   JON      C    456
1   JON      D    789
1   JON      B    545
1   JON      C    295

并且需要类似的输出

ID  NAME    B     C       D
== ====== ====== ====== ======
1   JON     123   456     789
1   JON     54    295    NULL

但是得到的输出

ID   NAME    B    C    D
== ======= ====  ===   ===
1   JON    123  NULL  NULL      
1   JON   NULL   456  NULL  
1   JON   NULL   NULL  789
1   JON   545   NULL   NULL 
1   JON  NULL   295    NULL

下面使用的SQL，当我运行此SQL时，我会得到额外的 NULL行

select ID, name ,case when typ ='B' then value end B,case when typ ='C' then value end  C,case when typ ='D' then value end D from table

Answer 1

您可以使用聚合。带row_number():

select ID, name,
max(case when typ = 'B' then value end) as B,
max(case when typ = 'C' then value end) as C,
max(case when typ = 'D' then value end) as D
from (select t.*,
row_number() over (partition by id, name, type order by value desc) as seqnum
from table t
) t
group by id, name, seqnum