select 员工, sum(case when 月份=1 then 记录数 end) as 1月, sum(case when 月份=2 then 记录数 end) as 2月 from table group by 员工,月份
select 员工, sum(case when 月份 = '1' then 记录数 end) as "1月", sum(case when 月份 = '2' then 记录数 end) as "2月" from table_name group by 员工;
http://topic.csdn.net/u/20110718/14/dd53ee7e-3d8c-4a11-9a9c-7f64824be6be.html--参照我上面的回答写个过程将sql语句拼出来。 --大致写了下,供参考。 SET serveroutput ON;DECLARE v_SQL VARCHAR2(32767); BEGIN FOR rec IN(SELECT DISTINCT 月份 as A FROM TABLE_NAME) LOOP v_SQL := v_SQL ||' SUM(CASE WHEN T.A = '''||REC.A||''' THEN 记录数 ELSE 0 END) AS "'||REC.A||'月" ,'; END LOOP;
v_SQL := 'SELECT 员工,' ||v_SQL; v_SQL := RTRIM(v_SQL,',') ||' FROM TABLE_NAME T GROUP BY T.员工' ;
dbms_output.put_line(v_SQL);END;
select k.employee , sum(case when k.month ='1' then k.record_no end ) as "1", sum(case when k.month='2' then k.record_no end) as "1" from Kelvin_Test k group by employee;結果: 1 2 A 100 100 B 200 300
你可以这样 select emp_no , sum(case when month='1' then recode else '0' end ) jun , sum(case when month= '2' then recode else '0' end ) feb
from
(select '1' month ,'A' emp_no ,'100' recode from dual union all select '1' month ,'B' emp_no ,'200' recode from dual union all select '2' month ,'A' emp_no ,'100' recode from dual union all select '2' month ,'B' emp_no ,'300' recode from dual ) A group by emp_no
BenChiM888 大虾的是正解。行列转换是SQL的经典问题了。没有其他好方法的。而且套路固定。 给你几个例子吧。2、不定列行列转换 如 c1 c2 --- ----------- 1 我 1 是 1 谁 2 知 2 道 3 不 …… 转换为 1 我是谁 2 知道 3 不 这一类型的转换可以借助于PL/SQL来完成,这里给一个例子 CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) RETURN VARCHAR2 IS Col_c2 VARCHAR2(4000); BEGIN FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP Col_c2 := Col_c2||cur.c2; END LOOP; Col_c2 := rtrim(Col_c2,1); RETURN Col_c2; END; select distinct c1 ,get_c2(c1) cc2 from table; 或者不用pl/sql,利用分析函数和 CONNECT_BY 实现: SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1 FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn FROM t)) START WITH rn1 IS NULL CONNECT BY rn1 = PRIOR rn GROUP BY c1; 3、列数不固定(交叉表行列转置) 这种是比较麻烦的一种,需要借助pl/sql: 原始数据: CLASS1 CALLDATE CALLCOUNT 1 2005-08-08 40 1 2005-08-07 6 2 2005-08-08 77 3 2005-08-09 33 3 2005-08-08 9 3 2005-08-07 21 转置后: CALLDATE CallCount1 CallCount2 CallCount3 ------------ ---------- ---------- ---------- 2005-08-09 0 0 33 2005-08-08 40 77 9 2005-08-07 6 0 21 试验如下: 1). 建立测试表和数据 CREATE TABLE t( class1 VARCHAR2(2 BYTE), calldate DATE, callcount INTEGER ); INSERT INTO t(class1, calldate, callcount) VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40); INSERT INTO t(class1, calldate, callcount) VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6); INSERT INTO t(class1, calldate, callcount) VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77); INSERT INTO t(class1, calldate, callcount) VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33); INSERT INTO t(class1, calldate, callcount) VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9); INSERT INTO t(class1, calldate, callcount) VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21); COMMIT ; 2). 建立ref cursor准备输出结果集 CREATE OR REPLACE PACKAGE pkg_getrecord IS TYPE myrctype IS REF CURSOR; END pkg_getrecord; / 3). 建立动态sql交叉表函数,输出结果集 CREATE OR REPLACE FUNCTION fn_rs RETURN pkg_getrecord.myrctype IS s VARCHAR2 (4000); CURSOR c1 IS SELECT ',sum(case when Class1=' || class1 || ' then CallCount else 0 end)' || ' "CallCount' || class1 || '"' c2 FROM t GROUP BY class1; r1 c1%ROWTYPE; list_cursor pkg_getrecord.myrctype; BEGIN s := 'select CallDate '; OPEN c1; LOOP FETCH c1 INTO r1; EXIT WHEN c1%NOTFOUND; s := s || r1.c2; END LOOP; CLOSE c1; s := s || ' from T group by CallDate order by CallDate desc '; OPEN list_cursor FOR s; RETURN list_cursor; END fn_rs; / 4). 测试在sql plus下执行: var results refcursor; exec :results := fn_rs; print results; CALLDATE CallCount1 CallCount2 CallCount3 --------------- ---------- ---------- ---------- 2005-08-09 0 0 33 2005-08-08 40 77 9 2005-08-07 6 0 21
sum(case when 月份=1 then 记录数 end) as 1月,
sum(case when 月份=2 then 记录数 end) as 2月
from table
group by 员工,月份
select 员工,
sum(case when 月份 = '1' then 记录数 end) as "1月",
sum(case when 月份 = '2' then 记录数 end) as "2月"
from table_name
group by 员工;
--大致写了下,供参考。
SET serveroutput ON;DECLARE
v_SQL VARCHAR2(32767);
BEGIN
FOR rec IN(SELECT DISTINCT 月份 as A FROM TABLE_NAME)
LOOP
v_SQL := v_SQL ||' SUM(CASE WHEN T.A = '''||REC.A||''' THEN 记录数 ELSE 0 END) AS "'||REC.A||'月" ,';
END LOOP;
v_SQL := 'SELECT 员工,' ||v_SQL;
v_SQL := RTRIM(v_SQL,',') ||' FROM TABLE_NAME T GROUP BY T.员工' ;
dbms_output.put_line(v_SQL);END;
sum(case when k.month ='1' then k.record_no end ) as "1",
sum(case when k.month='2' then k.record_no end) as "1"
from Kelvin_Test k
group by employee;結果:
1 2
A 100 100
B 200 300
select emp_no ,
sum(case when month='1' then recode else '0' end ) jun ,
sum(case when month= '2' then recode else '0' end ) feb
from
(select '1' month ,'A' emp_no ,'100' recode from dual union all
select '1' month ,'B' emp_no ,'200' recode from dual union all
select '2' month ,'A' emp_no ,'100' recode from dual union all
select '2' month ,'B' emp_no ,'300' recode from dual ) A
group by emp_no
给你几个例子吧。2、不定列行列转换
如
c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换可以借助于PL/SQL来完成,这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
select distinct c1 ,get_c2(c1) cc2 from table;
或者不用pl/sql,利用分析函数和 CONNECT_BY 实现:
SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME
FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1
FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn
FROM t))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY c1; 3、列数不固定(交叉表行列转置)
这种是比较麻烦的一种,需要借助pl/sql:
原始数据:
CLASS1 CALLDATE CALLCOUNT
1 2005-08-08 40
1 2005-08-07 6
2 2005-08-08 77
3 2005-08-09 33
3 2005-08-08 9
3 2005-08-07 21
转置后:
CALLDATE CallCount1 CallCount2 CallCount3
------------ ---------- ---------- ----------
2005-08-09 0 0 33
2005-08-08 40 77 9
2005-08-07 6 0 21
试验如下:
1). 建立测试表和数据
CREATE TABLE t(
class1 VARCHAR2(2 BYTE),
calldate DATE,
callcount INTEGER
);
INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);
INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);
INSERT INTO t(class1, calldate, callcount)
VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);
COMMIT ;
2). 建立ref cursor准备输出结果集
CREATE OR REPLACE PACKAGE pkg_getrecord
IS
TYPE myrctype IS REF CURSOR;
END pkg_getrecord;
/
3). 建立动态sql交叉表函数,输出结果集
CREATE OR REPLACE FUNCTION fn_rs
RETURN pkg_getrecord.myrctype
IS
s VARCHAR2 (4000);
CURSOR c1 IS
SELECT ',sum(case when Class1='
|| class1
|| ' then CallCount else 0 end)'
|| ' "CallCount'
|| class1
|| '"' c2
FROM t
GROUP BY class1;
r1 c1%ROWTYPE;
list_cursor pkg_getrecord.myrctype;
BEGIN
s := 'select CallDate ';
OPEN c1;
LOOP
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
s := s || r1.c2;
END LOOP;
CLOSE c1;
s := s || ' from T group by CallDate order by CallDate desc ';
OPEN list_cursor FOR s;
RETURN list_cursor;
END fn_rs;
/
4). 测试在sql plus下执行:
var results refcursor;
exec :results := fn_rs;
print results;
CALLDATE CallCount1 CallCount2 CallCount3
--------------- ---------- ---------- ----------
2005-08-09 0 0 33
2005-08-08 40 77 9
2005-08-07 6 0 21