Help with Insert Statement

[Waldar]

Then you have to use a macro or stored procedure (needed if you want to change the column name).

Datas

create multiset volatile table table1, no log
( MonYr     char(6)
, Row_Id    byteint
, Amount    integer
)
primary index (Row_Id)
on commit preserve rows;

insert into table1 values ('201801', 1, 1763);
insert into table1 values ('201801', 2,  185);
insert into table1 values ('201801', 3, 1429);
insert into table1 values ('201801', 4, 1616);
insert into table1 values ('201801', 5,  944);
insert into table1 values ('201801', 6,  129);
insert into table1 values ('201802', 1, 1535);
insert into table1 values ('201802', 2, 1232);
insert into table1 values ('201802', 3,  724);
insert into table1 values ('201802', 4, 1733);
insert into table1 values ('201802', 5,  357);
insert into table1 values ('201802', 6, 1748);
insert into table1 values ('201803', 1,  441);
insert into table1 values ('201803', 2, 1862);
insert into table1 values ('201803', 3, 1927);
insert into table1 values ('201803', 4,  758);
insert into table1 values ('201803', 5, 1818);
insert into table1 values ('201803', 6, 1921);
insert into table1 values ('201804', 1,  395);

Macro

replace macro m_pivot_table1 ( p_anchor     date ) AS
(

      select Row_Id
           , sum(case when to_char(           trunc(:p_anchor, 'mm')    , 'yyyymm') = MonYr then Amount else 0 end) as m_0
           , sum(case when to_char(add_months(trunc(:p_anchor, 'mm'), 1), 'yyyymm') = MonYr then Amount else 0 end) as m_1
           , sum(case when to_char(add_months(trunc(:p_anchor, 'mm'), 2), 'yyyymm') = MonYr then Amount else 0 end) as m_2
           , sum(case when to_char(add_months(trunc(:p_anchor, 'mm'), 3), 'yyyymm') = MonYr then Amount else 0 end) as m_3
           , sum(case when to_char(add_months(trunc(:p_anchor, 'mm'), 4), 'yyyymm') = MonYr then Amount else 0 end) as m_4
           , sum(case when to_char(add_months(trunc(:p_anchor, 'mm'), 5), 'yyyymm') = MonYr then Amount else 0 end) as m_5
        from table1
    group by Row_Id;

);

Results

exec m_pivot_table1 (date '2018-01-01');

 Row_Id m_0  m_1  m_2  m_3 m_4 m_5 
 ------ ---- ---- ---- --- --- --- 
      1 1763 1535  441 395   0   0
      2  185 1232 1862   0   0   0
      3 1429  724 1927   0   0   0
      4 1616 1733  758   0   0   0
      5  944  357 1818   0   0   0
      6  129 1748 1921   0   0   0

exec m_pivot_table1 (date '2017-11-15');

 Row_Id m_0 m_1 m_2  m_3  m_4  m_5 
 ------ --- --- ---- ---- ---- --- 
      1   0   0 1763 1535  441 395
      2   0   0  185 1232 1862   0
      3   0   0 1429  724 1927   0
      4   0   0 1616 1733  758   0
      5   0   0  944  357 1818   0
      6   0   0  129 1748 1921   0

Note you can insert the result into table2 inside the macro if needed.

[OscarBoots]

Thanks Waldar, that's great!

Is it possible to ise the Macro in a Loop?

Then the MonYr could change each loop??

Thanks Peter

[Waldar]

No, macros are kinda standalone.

What you can do is using the calendar table to generate the needed exec macro as a string, output the result in a file then bteq this file.

Or you can use a stored procedure.

[OscarBoots]

Thanks Waldar,

As I've marked this as solved, I'd like to continue on this subject with a new post called "Update Statement in Loop".

Thanks Peter