select b.depemp_id 部门ID, a.zc_id 职称ID, nvl(count(case when(a.zc_id=b.zcemp_id) then 1 end),0) 人数
from abc_zc0000 a, abc_emp000 b
where 1=1
group by a.zc_id, b.depemp_id
order by b.depemp_id, a.zc_id如图,想把形式改为
部门ID 职称ID1 职称ID2 职称ID3 职称ID4 职称ID5
1 0 1 1 0 0
2 .
这样的形式,其中部门与职称都是动态的还有对于group by的不显示Null也是非常纠结,用nvl不行,查了下说是要用外连接?希望大家能帮帮忙SQL行列转换
from abc_zc0000 a, abc_emp000 b
where 1=1
group by a.zc_id, b.depemp_id
order by b.depemp_id, a.zc_id如图,想把形式改为
部门ID 职称ID1 职称ID2 职称ID3 职称ID4 职称ID5
1 0 1 1 0 0
2 .
这样的形式,其中部门与职称都是动态的还有对于group by的不显示Null也是非常纠结,用nvl不行,查了下说是要用外连接?希望大家能帮帮忙SQL行列转换
http://www.cnblogs.com/zlja/archive/2011/12/10/2449515.html
----------------新建测试表
CREATE TABLE TEST(
WL VARCHAR2(10),
XYSL INTEGER,
XYCK VARCHAR2(10),
XCLCK VARCHAR2(10),
XCLCKSL INTEGER,
PC INTEGER
);----------------第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);
COMMIT;----------------行转列存储过程
CREATE OR REPLACE PROCEDURE P_TEST IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;BEGIN
V_SQL := 'SELECT WL,XYSL,XYCK';
FOR V_XCLCK IN CURSOR_1 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
END LOOP; V_SQL := V_SQL ||
' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW RESULT AS ' || V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;----------------执行存储过程
BEGIN
P_TEST;
END;----------------查看结果
SELECT * FROM RESULT;----------------第二部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124);
INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121);
INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322);
COMMIT;----------------执行存储过程
BEGIN
P_TEST;
END;
----------------查看结果
SELECT * FROM RESULT;
select 1 part,1 zc,0 cnt from dual
union all
select 1 part,2 zc,1 cnt from dual
union all
select 1 part,3 zc,1 cnt from dual
union all
select 2 part,3 zc,2 cnt from dual
union all
select 2 part,2 zc,1 cnt from dual)
SELECT part,sum(DECODE(zc,1,cnt,NULL)) zc1,
sum(DECODE(zc,2,cnt,NULL)) zc2,
sum(DECODE(zc,3,cnt,NULL)) zc3
from t GROUP BY part
这样可以了