时间戳

首页 > 解决方案 > 如何从 PLSQL 中的列生成列表？

sql - 如何从 PLSQL 中的列生成列表？

问题描述

我创建一个查询以将日期生成为行，

SELECT ltrim(TO_CHAR(MONTH,'mm-yyyy'),'0') AS process_date
FROM 
(
    select add_months (trunc (to_date('09/01/2019','dd/mm/yyyy'), 'MM'), 1*Level -1)
           Month
    FROM Dual
        CONNECT BY Level <= MONTHS_BETWEEN(to_date('09/01/2019','dd/mm/yyyy'), to_date('09/01/2018','dd/mm/yyyy')) + 1
    order by month
)

结果是

PROCESS_DATE|
------------|
1-2019      |
2-2019      |
3-2019      |
4-2019      |
5-2019      |
6-2019      |
7-2019      |
8-2019      |
9-2019      |
10-2019     |
11-2019     |
12-2019     |
1-2020      |

我需要你的帮助来创建一个 plsql 函数来将这些行转换为一个列表，比如

[1-2019,2-2019,3-2019,...]

标签： sqloracleplsql

解决方案

SELECT   LISTAGG(process_date, ',')  WITHIN GROUP (ORDER BY process_date)
  FROM   (SELECT   LTRIM (TO_CHAR (month, 'mm-yyyy'), '0') AS process_date
            FROM   (    SELECT   ADD_MONTHS (
                                     TRUNC (TO_DATE ('09/01/2019', 'dd/mm/yyyy'),
                                            'MM'),
                                     1 * LEVEL - 1)
                                     month
                          FROM   DUAL
                    CONNECT BY   LEVEL <=
                                     MONTHS_BETWEEN (
                                         TO_DATE ('09/01/2019', 'dd/mm/yyyy'),
                                         TO_DATE ('09/01/2018', 'dd/mm/yyyy'))
                                     + 1
                      ORDER BY   month))

推荐阅读