oracle 动态列的sql,oracle中的动态列使用sql

我认为写一个

pipelined table function that returns a variable structure是可能的,虽然很复杂.你的管道表函数将使用Oracle Data Cartridge接口和AnyDataSet类型的魔力在运行时返回一个动态结构.然后,您可以在后续sql语句中使用它,就好像它是一个表,即

SELECT *

FROM TABLE( your_pipelined_function( p_1,p_2 ));

还有几个参考文献讨论了相同的示例实现

--Create sample table.

create table branch_data as

select '100' BranchName,'1001010' CustomerNo from dual UNION ALL

select '100' BranchName,'1001011' CustomerNo from dual UNION ALL

select '103' BranchName,'1001012' CustomerNo from dual UNION ALL

select '104' BranchName,'1001013' CustomerNo from dual UNION ALL

select '104' BranchName,'1001014' CustomerNo from dual UNION ALL

select '104' BranchName,'1001015' CustomerNo from dual UNION ALL

select '105' BranchName,'1001016' CustomerNo from dual UNION ALL

select '105' BranchName,'1001017' CustomerNo from dual UNION ALL

select '106' BranchName,'1001018' CustomerNo from dual;

--Create a dynamic pivot in sql.

select *

from table(method4.dynamic_query(

q'[

--Create a select statement

select

--The SELECT:

'select'||chr(10)||

--The column list:

listagg(

replace(q'!sum(case when BranchName = '#BRANCH_NAME#' then 1 else 0 end) "#BRANCH_NAME#"!','#BRANCH_NAME#',BranchName),','||chr(10)) within group (order by BranchName)||chr(10)||

--The FROM:

'from branch_data' v_sql

from

(

--Distinct BranchNames.

select distinct BranchName

from branch_data

)

]'

));