oracle数据不定长截取后几位,求一按日期（不定长）横排的SQL语句

原帖由 风铃中の鬼 于 2011-3-29 11:02 发表

提供你一套我以前练习用的脚本做参考：

CREATE TABLE test_rc_change(

n_id NUMBER,

ne_name VARCHAR2(20),

first_result DATE,

lose NUMBER,

delay NUMBER

);

INSERT INTO test_rc_change VALUES(1,'北京',to_date('2010-1-1','yyyy-mm-dd'),0.2,2000);

INSERT INTO test_rc_change VALUES(2,'北京',to_date('2010-1-2','yyyy-mm-dd'),0.35,400);

INSERT INTO test_rc_change VALUES(3,'北京',to_date('2010-1-3','yyyy-mm-dd'),0.08,1200);

INSERT INTO test_rc_change VALUES(4,'上海',to_date('2010-1-1','yyyy-mm-dd'),0.13,500);

INSERT INTO test_rc_change VALUES(5,'上海',to_date('2010-1-2','yyyy-mm-dd'),0.72,450);

INSERT INTO test_rc_change VALUES(6,'广东',to_date('2010-1-3','yyyy-mm-dd'),0.22,1300);

INSERT INTO test_rc_change VALUES(7,'河北',to_date('2010-1-2','yyyy-mm-dd'),0.22,1300);

SELECT * FROM test_rc_change;

/*列数固定的行列转换*/

SELECT

first_result as "时间",

AVG(CASE WHEN ne_name = '北京' THEN lose END) as "北京",

AVG(CASE WHEN ne_name = '上海' THEN lose END) as "上海",

AVG(CASE WHEN ne_name = '广东' THEN lose END) as "广东"

FROM

test_rc_change

GROUP BY first_result

/*列数不固定的行列转换*/

CREATE OR REPLACE PROCEDURE t_pro_rc_change

AS

sqlstr VARCHAR2(4000);

--cname VARCHAR2(30);

s_sql VARCHAR2(4000);

cs_sql VARCHAR2(4000);

TYPE cs IS REF CURSOR;

TYPE vlist IS TABLE OF VARCHAR2(400);

rc_cs cs;

g1 vList;

BEGIN

cs_sql :='SELECT distinct ne_name FROM test_rc_change';

OPEN rc_cs FOR cs_sql;

FETCH rc_cs BULK COLLECT INTO g1;

CLOSE rc_cs;

FOR i IN g1.FIRST .. g1.LAST

LOOP

s_sql := s_sql||',AVG(CASE WHEN ne_name = '''||g1(i)||''' THEN lose END) as "'||g1(i)||'"'||chr(10);

END LOOP;

sqlstr := 'SELECT'||chr(10)

||'first_result as "时间"'||chr(10)

||s_sql

||'FROM test_rc_change'||chr(10)

||'GROUP BY first_result';

dbms_output.put_line(sqlstr);

--execute immediate('CREATE TABLE te_result AS '||sqlstr);

END;