如何在Oracle PL SQL中进行动态数据透视

本文介绍了如何在Oracle PL SQL中进行动态数据透视的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在下面有一个查询:而且我想使此透视图在过程中动态化

I have a query below: And I want to make this pivot dynamic in procedures

SELECT * FROM ( SELECT tcsd AS Aggregator, country, SUM (COUNT) AS total, COUNT (dest_addr) AS bnum, time_stamp FROM t_raw_intl_sms_aggr GROUP BY tcsd, COUNT, country, time_stamp ORDER BY tcsd, COUNT, country, time_stamp) PIVOT (SUM (total) AS total, COUNT (bnum) AS bnum_total FOR time_stamp IN (TO_DATE ('20150801', 'yyyymmdd') AS DAY_20150801_TOTAL, TO_DATE ('20150802', 'yyyymmdd') AS DAY_20150802_TOTAL, TO_DATE ('20150803', 'yyyymmdd') AS DAY_20150803_TOTAL, TO_DATE ('20150804', 'yyyymmdd') AS DAY_20150804_TOTAL, TO_DATE ('20150805', 'yyyymmdd') AS DAY_20150805_TOTAL, TO_DATE ('20150806', 'yyyymmdd') AS DAY_20150806_TOTAL, TO_DATE ('20150807', 'yyyymmdd') AS DAY_20150807_TOTAL, TO_DATE ('20150808', 'yyyymmdd') AS DAY_20150808_TOTAL, TO_DATE ('20150809', 'yyyymmdd') AS DAY_20150809_TOTAL, TO_DATE ('20150810', 'yyyymmdd') AS DAY_20150810_TOTAL, TO_DATE ('20150811', 'yyyymmdd') AS DAY_20150811_TOTAL, TO_DATE ('20150812', 'yyyymmdd') AS DAY_20150812_TOTAL, TO_DATE ('20150813', 'yyyymmdd') AS DAY_20150813_TOTAL, TO_DATE ('20150814', 'yyyymmdd') AS DAY_20150814_TOTAL, TO_DATE ('20150815', 'yyyymmdd') AS DAY_20150815_TOTAL, TO_DATE ('20150816', 'yyyymmdd') AS DAY_20150816_TOTAL, TO_DATE ('20150817', 'yyyymmdd') AS DAY_20150817_TOTAL, TO_DATE ('20150818', 'yyyymmdd') AS DAY_20150818_TOTAL, TO_DATE ('20150819', 'yyyymmdd') AS DAY_20150819_TOTAL, TO_DATE ('20150820', 'yyyymmdd') AS DAY_20150820_TOTAL, TO_DATE ('20150821', 'yyyymmdd') AS DAY_20150821_TOTAL, TO_DATE ('20150822', 'yyyymmdd') AS DAY_20150822_TOTAL, TO_DATE ('20150823', 'yyyymmdd') AS DAY_20150823_TOTAL, TO_DATE ('20150824', 'yyyymmdd') AS DAY_20150824_TOTAL, TO_DATE ('20150825', 'yyyymmdd') AS DAY_20150825_TOTAL, TO_DATE ('20150826', 'yyyymmdd') AS DAY_20150826_TOTAL, TO_DATE ('20150827', 'yyyymmdd') AS DAY_20150827_TOTAL, TO_DATE ('20150828', 'yyyymmdd') AS DAY_20150828_TOTAL, TO_DATE ('20150829', 'yyyymmdd') AS DAY_20150829_TOTAL, TO_DATE ('20150830', 'yyyymmdd') AS DAY_20150830_TOTAL, TO_DATE ('20150831', 'yyyymmdd') AS DAY_20150831_TOTAL)) ORDER BY aggregator, country

上面的结果是:

SYBASE Papua New Guinea 1 1 2 1 1 1 1 SYBASE Peru 2 13 9 20 43 36 12 19 27 18 23 73 33 41 18 17 12 31 35 41 15 40 23 34 24 31 63 31 20 SYBASE Poland 2 3 8 8 2 10 23 10 17 13 21 21 26 6 11 13 17 14 18 11 7 7 10 4 SYBASE Portugal (includes Azores) 8 7 15 13 14 59 47 40 32 39 50 78 41 47 26 42 68 56 64 51 41 62 90 74 62 40 44 46 55

我已经制作了示例存储过程:

I already made sample stored procedure:

CREATE OR REPLACE PROCEDURE intl_sms_aggr (p_cursor IN OUT SYS_REFCURSOR) AS l_query LONG := 'SELECT * FROM ( SELECT tcsd AS Aggregator, country, SUM (COUNT) AS total, COUNT (dest_addr) AS bnum, time_stamp FROM t_raw_intl_sms_aggr GROUP BY tcsd, COUNT, country, time_stamp ORDER BY tcsd, COUNT, country, time_stamp) PIVOT (SUM (total) AS total, COUNT (bnum) AS bnum_total FOR time_stamp IN ('; BEGIN FOR x IN (select time_stamp from prev_month) LOOP l_query := l_query|| REPLACE (' TO_DATE(''$X$'',''yyyymmdd'') as DAY_$X$_TOTAL, ','$X$',x.time_stamp); END LOOP; l_query := l_query || ')) ORDER BY aggregator, country'; -- OPEN p_cursor FOR l_query; DBMS_OUTPUT.put_line ('query: ' || l_query); END; /

但是我在最后一个循环中有一个逗号问题，请帮助我重构脚本，这是我的问题:

But I have a problem in my last loop which is the comma, please help me to reconstruct my script, here is my problem:

SELECT * FROM ( SELECT tcsd AS Aggregator, country, SUM (COUNT) AS total, COUNT (dest_addr) AS bnum, time_stamp FROM t_raw_intl_sms_aggr GROUP BY tcsd, COUNT, country, time_stamp ORDER BY tcsd, COUNT, country, time_stamp) PIVOT (SUM (total) AS total, COUNT (bnum) AS bnum_total FOR time_stamp IN (TO_DATE ('20150801', 'yyyymmdd') AS DAY_20150801_TOTAL, TO_DATE ('20150802', 'yyyymmdd') AS DAY_20150802_TOTAL, TO_DATE ('20150803', 'yyyymmdd') AS DAY_20150803_TOTAL, TO_DATE ('20150804', 'yyyymmdd') AS DAY_20150804_TOTAL, TO_DATE ('20150805', 'yyyymmdd') AS DAY_20150805_TOTAL, TO_DATE ('20150806', 'yyyymmdd') AS DAY_20150806_TOTAL, TO_DATE ('20150807', 'yyyymmdd') AS DAY_20150807_TOTAL, TO_DATE ('20150808', 'yyyymmdd') AS DAY_20150808_TOTAL, TO_DATE ('20150809', 'yyyymmdd') AS DAY_20150809_TOTAL, TO_DATE ('20150810', 'yyyymmdd') AS DAY_20150810_TOTAL, TO_DATE ('20150811', 'yyyymmdd') AS DAY_20150811_TOTAL, TO_DATE ('20150812', 'yyyymmdd') AS DAY_20150812_TOTAL, TO_DATE ('20150813', 'yyyymmdd') AS DAY_20150813_TOTAL, TO_DATE ('20150814', 'yyyymmdd') AS DAY_20150814_TOTAL, TO_DATE ('20150815', 'yyyymmdd') AS DAY_20150815_TOTAL, TO_DATE ('20150816', 'yyyymmdd') AS DAY_20150816_TOTAL, TO_DATE ('20150817', 'yyyymmdd') AS DAY_20150817_TOTAL, TO_DATE ('20150818', 'yyyymmdd') AS DAY_20150818_TOTAL, TO_DATE ('20150819', 'yyyymmdd') AS DAY_20150819_TOTAL, TO_DATE ('20150820', 'yyyymmdd') AS DAY_20150820_TOTAL, TO_DATE ('20150821', 'yyyymmdd') AS DAY_20150821_TOTAL, TO_DATE ('20150822', 'yyyymmdd') AS DAY_20150822_TOTAL, TO_DATE ('20150823', 'yyyymmdd') AS DAY_20150823_TOTAL, TO_DATE ('20150824', 'yyyymmdd') AS DAY_20150824_TOTAL, TO_DATE ('20150825', 'yyyymmdd') AS DAY_20150825_TOTAL, TO_DATE ('20150826', 'yyyymmdd') AS DAY_20150826_TOTAL, TO_DATE ('20150827', 'yyyymmdd') AS DAY_20150827_TOTAL, TO_DATE ('20150828', 'yyyymmdd') AS DAY_20150828_TOTAL, TO_DATE ('20150829', 'yyyymmdd') AS DAY_20150829_TOTAL, TO_DATE ('20150830', 'yyyymmdd') AS DAY_20150830_TOTAL, TO_DATE ('20150831', 'yyyymmdd') AS DAY_20150831_TOTAL,)) ORDER BY aggregator, country

推荐答案

按如下所示更改您的proc

Change your proc like below

BEGIN -- Use another variable and initialize with count(*) from prev_month (say totalCount) -- Initialize another counter say curCount = 0 -- FOR x IN (select time_stamp from prev_month) LOOP -- increment curCount. If curCount = totalCount -- then use -- l_query := l_query|| REPLACE (' TO_DATE(''$X$'',''yyyymmdd'') as DAY_$X$_TOTAL ','$X$',x.time_stamp); --your code without comma at the end. -- else l_query := l_query|| REPLACE (' TO_DATE(''$X$'',''yyyymmdd'') as DAY_$X$_TOTAL, ','$X$',x.time_stamp); -- end if. END LOOP;

确切语法

BEGIN curCount := 0; SELECT COUNT (*) INTO o_count FROM prev_month; FOR x IN (select time_stamp from prev_month) LOOP curCount := curCount +1; -- increment curCount. IF curCount = o_count THEN l_query :=l_query|| REPLACE (' TO_DATE(''$X$'',''yyyymmdd'') as DAY_$X$_TOTAL ','$X$',x.time_stamp); else l_query := l_query|| REPLACE (' TO_DATE(''$X$'',''yyyymmdd'') as DAY_$X$_TOTAL, ','$X$',x.time_stamp); end if. END LOOP;