oracle - 如何在oracle PL SQL中进行动态支点

Question

我在下面有一个查询:我想在程序中使这个枢轴动态化

  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

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

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;
/

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

 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

Answer 1

像下面一样改变你的过程

          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;