17,377
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE tt;
CREATE TABLE tt(BH VARCHAR2(20), GZ VARCHAR2(20), RS VARCHAR2(20));
INSERT INTO TT VALUES ('PDA03116', '注册兽医师', 3);
INSERT INTO TT VALUES ('PDA03116', '无定级兽医', 1);
INSERT INTO TT VALUES ('PDA03116', '中级技工', 2);
INSERT INTO TT VALUES ('PDA03116', '初级技工 ', 1);
INSERT INTO TT VALUES ('PDA03116', '无定级技工', 2);
INSERT INTO TT VALUES ('PDA03117', '无定级技工', 7);
COMMIT;
SELECT BH,
MAX(DECODE(GZ, '注册兽医师', RS)),
MAX(DECODE(GZ, '无定级兽医', RS)),
MAX(DECODE(GZ, '中级技工', RS)),
MAX(DECODE(GZ, '初级技工', RS)),
MAX(DECODE(GZ, '无定级技工', RS))
FROM TT
GROUP BY BH
ORDER BY 1;
[Quote=引用楼主 gisinfo 的帖子:]select Distinct t01,
max(case a when '1' then t03 end) A,
max(case a when '2' then t03 end) B,
max(case a when '3' then t03 end) C,
max(case a when '4' then t03 end) D,
max(case a when '5' then t03 end) E,
max(case a when '6' then t03 end) F,
max(case a when '7' then t03 end) G
from (
select test.*,to_char(rownum) a from test)
group by t01
RESULT:
PDA03116 2 3 1 2 1
PDA03117 2 7
select Distinct t01,
max(case a when '1' then t03 end) A,
max(case a when '2' then t03 end) B,
max(case a when '3' then t03 end) C,
max(case a when '4' then t03 end) D,
max(case a when '5' then t03 end) E,
max(case a when '6' then t03 end) F,
max(case a when '7' then t03 end) G
from (
select test.*,to_char(rownum) a from test)
group by t01
with test as (select 'PDA03116' BH,'注册兽医师' GZ,3 RS from dual
union all
select 'PDA03116' BH,'无定级兽医' GZ,1 RS from dual
union all
select 'PDA03116' BH,'中级技工' GZ,2 RS from dual
union all
select 'PDA03116' BH,'初级技工' GZ,1 RS from dual
union all
select 'PDA03116' BH,'无定级技工' GZ,2 RS from dual
union all
select 'PDA03117' BH,'无定级技工' GZ,7 RS from dual)
select bh,sum(decode(gz,'注册兽医师',rs,0)) "j_1(注册兽医师)",
sum(decode(gz,'无定级兽医',rs,0)) "j_2(无定级兽医)",
sum(decode(gz,'中级技工',rs,0)) "j_3(中级技工)",
sum(decode(gz,'初级技工',rs,0)) "j_4(初级技工)",
sum(decode(gz,'无定级技工',rs,0)) "j_5(无定级技工)" from test
group by bh;