Oracle 需要在报表中创建动态列

嗨，汤姆，

我是SQL和PLSQL的新手，我需要创建一个报告，其中包括基于程序持续时间的月数的月动态列。

我实际上尝试在LiveSQL中创建脚本，但是现在它无法访问，因此我提供了所有准备运行的示例脚本。

Create table program_det (program_name varchar2(50), program_date NUMBER);
/

Insert into program_det values('PROGRAM_1', 201801);
Insert into program_det values('PROGRAM_1', 201807);
COMMIT;
/

-
我的确切意思是

我有列 <程序名称> 、 <程序日期>

假设我有一个程序的持续时间为6个月，例如从201801到201806，该月的总数为6。在这种情况下，我需要在报告中动态生成6列，如下所示。

Program_Name Program_Date Month_1 Month_2 Month_3 Month_4 Month_5 Month_6
Program_1 12018  0 0 0 0 0
Program_1 62018  0 0 0 0 0

-
我尝试使用以下方法，在这里我可以找到月数，但我需要帮助基于此月数创建动态列。

select program_name, 
      months_between(TO_DATE(max(program_date),'YYYYMM'),TO_DATE(min(program_date),'YYYYMM')) 
       number_months
from program_det
where program_name ='PROGRAM_1'
group by program_name;

-

我不确定，如果这可能是非常愚蠢和简单的，但我无法找到解决方案。

请帮忙!

问候，
卡维亚

这里的挑战是，你需要做一个 * 动态 * 枢轴，即，列数是未知的，直到你看数据。Stew Ashton在这里写了一篇关于如何做到这一点的好博客文章

https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/

我会用它来帮助解决这个问题。我也使用了日期数据类型，因为从来没有使用数字数据。

首先，我可以生成我需要的月份

SQL> Create table program_det (program_name varchar2(50), program_date date);

Table created.

SQL> Insert into program_det values('PROGRAM_1', date '2018-01-01');

1 row created.

SQL> Insert into program_det values('PROGRAM_1', date '2018-07-01');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> with pgm as
  2  ( select program_name,
  3                 min(program_date) mind,
  4                 max(program_date) maxd
  5          from program_det
  6          group by program_name )
  7  select *
  8  from  pgm p,
  9        table(cast(multiset(
 10            select add_months(p.mind,rownum-1)
 11            from   pgm
 12            where  program_name = p.program_name
 13            connect by add_months(mind,rownum-1) <= p.maxd
 14            ) as sys.odcidatelist));

PROGRAM_NAME                                       MIND      MAXD      COLUMN_VA
-------------------------------------------------- --------- --------- ---------
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-JAN-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-FEB-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-MAR-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-APR-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-MAY-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-JUN-18
PROGRAM_1                                          01-JAN-18 01-JUL-18 01-JUL-18

7 rows selected.

现在，为了便于阅读，我将创建一个视图

SQL> create or replace view v as
  2  with pgm as
  3  ( select program_name,
  4                 min(program_date) mind,
  5                 max(program_date) maxd
  6          from program_det
  7          group by program_name )
  8  select program_name,
  9         to_char(column_value,'YYYYMM') mth
 10  from  pgm p,
 11        table(cast(multiset(
 12            select add_months(p.mind,rownum-1)
 13            from   pgm
 14            where  program_name = p.program_name
 15            connect by add_months(mind,rownum-1) <= p.maxd
 16            ) as sys.odcidatelist));

View created.

然后我将它传递到通用枢轴中

SQL> set serveroutput off
SQL> var rc refcursor
SQL> begin
  2        :rc := generic_pivot(
  3          '(select program_name, mth, 0 val from v)',
  4          'MTH',
  5          'SUM(VAL)'
  6        );
  7      end;
  8  /

PL/SQL procedure successfully completed.

SQL> print rc

PROGRAM_NAME                                           201801     201802     201803     201804     201805     201806     201807
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PROGRAM_1

我在那里硬编码了零，但是你会加入或类似的方式来获得你想要呈现的数字数据。