oracle把多行转成多列,Oracle 多行转多列

1 create or replace procedure NAG_QUESTIONERSULT_EXP(

2 V_QID in number,

3 C_Title out sys_refcursor,

4 C_Data out sys_refcursor

5 )

6 as

7 /*

8 导出调查问卷资答案数据

9 Auth:lzpong 2015/09/01

10 */

11 cursor dusers is --列信息

12 select qss.qss_id,nvl(qss.qss_title,substr(qs.qs_title,0,20)) QSS_TITLE from nag_questions qs,nag_questionss qss

13 where qss.qss_qs_id=qs.qs_id and qs.qs_q_id=V_QID

14 order by qs.qs_order,qss.qss_order;

15 strSql varchar2(10000);

16 begin

17 for ur in dusers loop

18 strSql:=strSql||‘wm_concat(decode(QR_QSS_ID,‘‘‘||ur.qss_id||‘‘‘,QR_QSS_DES,null)) as "‘||ur.qss_title||‘",‘;

19 end loop;

20 open C_Data for

21 ‘select QR_ID,QR_AGENTID,max(QR_CREATEDATE) QR_CREATEDATE,‘||

22 substr(strSql,0,length(strSql)-1)

23 ||‘from (

24 select QR_QS_ID,QR_QSS_ID QR_QSS_ID,‘‘(√)‘‘||QR_QSS_DES QR_QSS_DES, QR_CREATEDATE,QR_U_ID,QR_AGENTID,QR_ID

25 from nag_questionresult,nag_questions where qr_qs_id=qs_id and qs_q_id=‘||V_QID||‘

26 order by QR_ID

27 )

28 group by QR_ID,QR_AGENTID‘;

29 open C_Title for

30 select q.q_id,q.q_title,q.q_des,qs.qs_id,qs.qs_title ,(select count(qss_id) from nag_questionss qss where qss.qss_qs_id=qs.qs_id) qs_cols

31 from nag_question q,nag_questions qs where q.q_id=qs.qs_q_id and q.q_id=V_QID

32 order by q.q_id,qs.qs_order;

33 end;