oracle中的动态列使用sql

我有以下表格的例子. Thera可以是无限的分支和客户.我需要对这个分支进行分组并统计他们的客户,然后用不同的列显示它.

BRANCHNAME  CUSTOMERNO
100         1001010
100         1001011
103         1001012
104         1001013
104         1001014
104         1001015
105         1001016
105         1001017
106         1001018

注意,可以有无限的分支和客户,查询必须不仅工作这种情况.

在这种情况下,接受的结果是：

100 103 104 105 106
 2   1   3   2   1

示例sql DATA

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

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

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

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

> Dynamic SQL Pivoting
> Oracle Data Cartridge开发人员指南的Implementing the Interface Approach部分
> Method4.下载并安装开源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
        )
    ]'
));

oracle中的动态列使用sql

猜你在找的Oracle相关文章