1:创建试验表-> -- << Create Table CREATE TABLE MAIN.TEST_CROSSTAB ( ID NUMBER, Date_ins DATE, Num NUMBER )
2:插入试验数据-> -- << Insert data INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-1','YYYY-MM-DD'),100); INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-2','YYYY-MM-DD'),200); INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-3','YYYY-MM-DD'),100); INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-1','YYYY-MM-DD'),400); INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-2','YYYY-MM-DD'),100); INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-3','YYYY-MM-DD'),100); COMMIT;
3:创建package -- << Create package CREATE OR REPLACE PACKAGE test_ct AS TYPE rc IS REF CURSOR; PROCEDURE cross_tab( p_query OUT rc);END test_ct;
4:创建package body -- << Create package body CREATE OR REPLACE PACKAGE BODY test_ct AS
PROCEDURE Cross_Tab(p_query OUT rc) IS str VARCHAR2(1000); BEGIN /* SELECT id, MAX( DECODE( date_ins, TO_DATE('2005-1-1','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-1", MAX( DECODE( date_ins, TO_DATE('2005-1-2','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-2", MAX( DECODE( date_ins, TO_DATE('2005-1-3','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-3" FROM( SELECT id, date_ins, SUM(num) cnt FROM TEST_CROSSTAB GROUP BY id, date_ins) GROUP BY id; */ str := 'SELECT id, ';
FOR r IN (SELECT DISTINCT date_ins FROM TEST_CROSSTAB ORDER BY date_ins )LOOP str := str || 'MAX( DECODE( date_ins,TO_DATE('''; str := str || TO_CHAR(r.date_ins,'YYYY-MM-DD')||''',''YYYY-MM-DD'''; str := str || '), cnt, NULL ) ) AS "'||TO_CHAR(r.date_ins,'YYYY-MM-DD')||'",'; END LOOP;
str := SUBSTR(str,1,LENGTH(str)-1); str := str || 'FROM( SELECT id, date_ins, SUM(num) cnt FROM TEST_CROSSTAB GROUP BY id, date_ins) GROUP BY id'; /* dbms_output.put_line('Str1 = '||SUBSTR(str,1,100)); dbms_output.put_line('Str2 = '||SUBSTR(str,101,100)); dbms_output.put_line('Str3 = '||SUBSTR(str,201,100)); dbms_output.put_line('Str4 = '||SUBSTR(str,301,100)); dbms_output.put_line('Str5 = '||SUBSTR(str,401,100)); */ OPEN p_query FOR str;
END;END test_ct;
5:执行 -- << Execute in SQL*PLUS variable x refcursor SET autoprint ON EXEC test_ct.cross_tab(:x);
6:结果 -- << It should display like ID 2005-01-01 2005-01-02 2005-01-03 ---------- ---------- ---------- ---------- 1 100 200 100 2 400 100 100
http://blog.csdn.net/ningoo/archive/2004/12/31/236440.aspx
-- << Create Table
CREATE TABLE MAIN.TEST_CROSSTAB
(
ID NUMBER,
Date_ins DATE,
Num NUMBER
)
-- << Insert data
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-1','YYYY-MM-DD'),100);
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-2','YYYY-MM-DD'),200);
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (1,TO_DATE('2005-1-3','YYYY-MM-DD'),100);
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-1','YYYY-MM-DD'),400);
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-2','YYYY-MM-DD'),100);
INSERT INTO TEST_CROSSTAB (id,date_ins,num) VALUES (2,TO_DATE('2005-1-3','YYYY-MM-DD'),100);
COMMIT;
-- << Create package
CREATE OR REPLACE PACKAGE test_ct AS TYPE rc IS REF CURSOR;
PROCEDURE cross_tab( p_query OUT rc);END test_ct;
-- << Create package body
CREATE OR REPLACE PACKAGE BODY test_ct AS
PROCEDURE Cross_Tab(p_query OUT rc) IS
str VARCHAR2(1000); BEGIN
/*
SELECT id,
MAX( DECODE( date_ins, TO_DATE('2005-1-1','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-1",
MAX( DECODE( date_ins, TO_DATE('2005-1-2','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-2",
MAX( DECODE( date_ins, TO_DATE('2005-1-3','YYYY-MM-DD'), cnt, NULL ) ) AS "2005-1-3"
FROM( SELECT id, date_ins, SUM(num) cnt
FROM TEST_CROSSTAB GROUP BY id, date_ins)
GROUP BY id;
*/
str := 'SELECT id, ';
FOR r IN (SELECT DISTINCT date_ins FROM TEST_CROSSTAB ORDER BY date_ins )LOOP
str := str || 'MAX( DECODE( date_ins,TO_DATE(''';
str := str || TO_CHAR(r.date_ins,'YYYY-MM-DD')||''',''YYYY-MM-DD''';
str := str || '), cnt, NULL ) ) AS "'||TO_CHAR(r.date_ins,'YYYY-MM-DD')||'",';
END LOOP;
str := SUBSTR(str,1,LENGTH(str)-1);
str := str || 'FROM( SELECT id, date_ins, SUM(num) cnt FROM TEST_CROSSTAB GROUP BY id, date_ins) GROUP BY id';
/*
dbms_output.put_line('Str1 = '||SUBSTR(str,1,100));
dbms_output.put_line('Str2 = '||SUBSTR(str,101,100));
dbms_output.put_line('Str3 = '||SUBSTR(str,201,100));
dbms_output.put_line('Str4 = '||SUBSTR(str,301,100));
dbms_output.put_line('Str5 = '||SUBSTR(str,401,100));
*/
OPEN p_query FOR str;
END;END test_ct;
-- << Execute in SQL*PLUS
variable x refcursor
SET autoprint ON
EXEC test_ct.cross_tab(:x);
-- << It should display like ID 2005-01-01 2005-01-02 2005-01-03
---------- ---------- ---------- ----------
1 100 200 100
2 400 100 100