postgres 列转行操作记录

数据表如下:

 

现在要把每个学生的三列成绩转换成刚，并对应上各自课程的成绩，语句如下：

with test12 as (
select '10001' as xh ,'张三' as xm ,'80' as ywkcj ,'90' as yykcj,'100' sxkccj
union all
select '10002' as xh ,'李四' as xm ,'85' as ywkcj ,'95' as yykcj,'102' sxkccj
union all
select '10003' as xh ,'王五' as xm ,'96' as ywkcj ,'69' as yykcj,'89' sxkccj
)
SELECT xh,xm,unnest(ARRAY ['语文课成绩'::TEXT, '英语课成绩'::TEXT, '数学课成绩'::TEXT]) AS kpi_details,
unnest(
ARRAY [test12.ywkcj::NUMERIC(30, 16), test12.yykcj::NUMERIC(30, 16), test12.sxkccj::NUMERIC(30, 16)]) AS value
FROM test12

转换后的结果