使用SQL在Oracle中创建动态列

我认为写一个返回变量结构的流水线表函数虽然很复杂，但却是可能的。您的管道表函数将使用Oracle
Data Cartridge接口和AnyDataSet类型的魔力在运行时返回动态结构。然后，您可以在后续的SQL语句中使用它，就像它是一个表一样，即

SELECt *  FROM TABLE( your_pipelined_function( p_1, p_2 ));

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

• 动态SQL透视
• 《Oracle数据盒带开发人员指南》的“实现接口方法”部分

• 方法4。 下载并安装开源PL / SQL代码后，这里是一个完整的实现：

  --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
  )
  ]’
  ));