第二种方法
select s.c_empid,sum(decode(dp.c_name,'基本工资一基础工资',nvl(s.c_value, 0))) as "基础工资",
sum(decode(dp.c_name,'基本工资一加班费',nvl(s.c_value, 0))) as "加班费",
sum(decode(dp.c_name,'基本工资一岗位月度工资',nvl(s.c_value, 0))) as "岗位月度工资"
from tb_cnb_otherpays_component dp
left join (select C_OID,C_COMPENTID,C_GROUPID,C_YEAR,C_MONTH,C_VALUE,C_OPERATORID,C_OPERATETIME,C_EMPID,C_STATUS
from tb_cnb_otherpays_details ds
where ds.c_year = 2014
AND DS.C_GROUPID = 156075017
AND DS.C_MONTH = 5
and ds.c_empid in(21279974,21278721)
) s
on dp.c_oid = s.c_compentid
group by s.c_empid
需要把上图的数据结构变成下图的表结构
select c_empid, sum(CASE WHEN dp.c_name='基本工资一岗位月度工资' THEN nvl(s.c_value, 0) END)as "岗位月度工资",
sum(CASE WHEN dp.c_name='基本工资一加班费' THEN nvl(s.c_value, 0) END)as "加班费",
sum(CASE WHEN dp.c_name='基本工资一基础工资' THEN nvl(s.c_value, 0) END)as "基础工资"
from tb_cnb_otherpays_component dp
left join (select C_OID,C_COMPENTID,C_GROUPID,C_YEAR,C_MONTH,C_VALUE,C_OPERATORID,C_OPERATETIME,C_EMPID,C_STATUS
from tb_cnb_otherpays_details ds
where ds.c_year = 2014
AND DS.C_GROUPID = 156075017
AND DS.C_MONTH = 5
and ds.c_empid in(21279974)
) s
on dp.c_oid = s.c_compentid
group by s.c_empid;
第三种 把多行转成一列

select id, vnum, to_char(wmsys.wm_concat(vname)) c
from tab_test group by
id,vnum;
第四种 把一列转成多行

with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
select
regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum <=
length(regexp_replace(id,'[^/]+'))