oracle pivot动态sql,sql – 动态Oracle Pivot_In_Clause

您可以在脚本中构建动态查询,

看看这个例子：

variable rr refcursor

declare

bb varchar2(4000);

cc varchar2( 30000 );

begin

WITH PIVOT_DATA AS (

SELECT *

FROM

(

SELECT USERNAME, GRANTED_ROLE

FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R

ON U.USERNAME = R.GRANTEE

)

)

select ''''|| listagg( granted_role, ''',''' )

within group( order by granted_role ) || '''' as x

into bb

from (

select distinct granted_role from pivot_data

)

;

cc := q'[

WITH PIVOT_DATA AS (

SELECT *

FROM

(

SELECT USERNAME, GRANTED_ROLE

FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R

ON U.USERNAME = R.GRANTEE

)

)

SELECT *

FROM PIVOT_DATA

PIVOT

(

COUNT(GRANTED_ROLE)

FOR GRANTED_ROLE

IN(]' || bb || q'[) -- Just an example

)

ORDER BY USERNAME ASC]';

open :rr for cc;

end;

/

SET PAGESIZE 200

SET LINESIZE 16000

print :rr

这是结果(只有小片段,因为它很宽很长)

-----------------------------------------------------------------------------------------------------------------------------------

USERNAME 'ADM_PARALLEL_EXECUTE_TASK' 'APEX_ADMINISTRATOR_ROLE' 'AQ_ADMINISTRATOR_ROLE' 'AQ_USER_ROLE'

------------------------------ --------------------------- ------------------------- ----------------------- ----------------------

ANONYMOUS 0 0 0 0

APEX_030200 0 0 0 0

APEX_PUBLIC_USER 0 0 0 0

APPQOSSYS 0 0 0 0

..............

IX 0 0 1 1

OWBSYS 0 0 1 1