How to make dynamic pivot in 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

and the results above is:

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

回答1:

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;

EDIT: Exact syntax

           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;

回答2:

Or use RTRIM:

            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_uqery := RTRIM(l_query,',');

               l_query := l_query || ')) ORDER BY aggregator, country';

              -- OPEN p_cursor FOR l_query;
                                   DBMS_OUTPUT.put_line ('query: ' || l_query);
                                   END;