oracle中使用sql的动态列

2022-01-22 00:00:00 pivot sql oracle

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

I have following example of table. Thera can be unlimited branch and customers. I need group this branches and count their customers, then show it's with different columns.

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

请注意，可以有无限的分支和客户，查询必须不仅适用于这种情况.

Note that there can be unlimited branch and customers, the query must work not only this case.

在这种情况下，接受的结果是:

In this case the accepted result is:

100 103 104 105 106
 2   1   3   2   1

示例 SQL 数据

    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

推荐答案

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

I think it is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function will use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.

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

更多参考资料讨论了相同的示例实现

A couple more references that discuss the same sample implementation

  • 动态 SQL 透视
  • 实现接口方法部分Oracle Data Cartridge 开发人员指南

  • Method4. 下载安装开源PL/SQL代码后，这里有一个完成实施:

  • Dynamic SQL Pivoting
  • The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

  • Method4. After downloading and installing the open source PL/SQL code, here is a complete implementation:

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

相关文章