使用SQL在Oracle中创建动态列 [英] dynamic columns in oracle using sql

问题描述

我有以下表格示例. 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   

推荐答案

我认为有可能写一个

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

这篇关于使用SQL在Oracle中创建动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！