我有以下表格示例。 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 数据示例
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
我认为虽然相当复杂,但还是可以写一个返回变量结构的流水线表函数 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4843682300346852395#5421020800346627246。您的管道表函数将使用 Oracle Data Cartridge 接口和 AnyDataSet 类型的魔力在运行时返回动态结构。然后您可以在后续 SQL 语句中使用它,就像它是一个表一样,即
SELECT *
FROM TABLE( your_pipelined_function( p_1, p_2 ));
更多讨论相同示例实现的参考资料
-
动态 SQL 透视 http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
- The 实现接口方法 http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/pipe_paral_tbl.htm#ADDCI4701Oracle Data Cartridge 开发人员指南的部分
-
Method4. https://github.com/method5/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
)
]'
));
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)