oracle中的动态列使用sql
发布时间:2020-12-12 15:14:14 所属栏目:百科 来源:网络整理
导读:我有以下表格的例子. Thera可以是无限的分支和客户.我需要对这个分支进行分组并统计他们的客户,然后用不同的列显示它. BRANCHNAME CUSTOMERNO100 1001010100 1001011103 1001012104 1001013104 1001014104 1001015105 1001016105 1001017106 1001018 注意,可
我有以下表格的例子. 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 --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 ) ]' )); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读