UNPIVOT data then PIVOT (over 7 days)

DROP TABLE DQ_REPORT_DAILY purge;
CREATE TABLE DQ_REPORT_DAILY as(
SELECT table_name as TABLE_NAME,
          CASE WHEN action_item like 'SOURCE_COUNT' then 'Source Row Count'
              WHEN action_item like 'TARGET_COUNT' then 'Target Row Count'
              WHEN action_item like 'S_MINUS_T_COUNT' then 'Source minus Target Count'
              WHEN action_item like 'T_MINUS_S_COUNT' then 'Target minus Source Count'
              WHEN action_item like  'BWD_COUNT' then 'Both with Differences Count' END AS ACTION_ITEM ,
         MAX(curr_week) week_0,
         MAX(week_ago_1) week_1,
         MAX(week_ago_2) week_2,
         MAX(week_ago_3) week_3,
         MAX(week_ago_4) week_4,
         MAX(week_ago_5) week_5
    FROM (SELECT run_dt,
                 table_name,
                 action_item,
                 val,
                 (NEXT_DAY(TRUNC(SYSDATE - 7), 'Sunday') - NEXT_DAY(TRUNC(run_dt - 7), 'Sunday'))/7
                     run_wk
            FROM DQ_HIGH_LEVEL_COUNTS UNPIVOT (val
                           FOR action_item
                           IN (source_count,
                              target_count,
                              s_minus_t_count,
                              t_minus_s_count,
                              bwd_count))) PIVOT (MAX(val)
                                               FOR run_wk
                                               IN (0 curr_week,
                                                  1 week_ago_1,
                                                  2 week_ago_2,
                                                  3 week_ago_3,
                                                  4 week_ago_4,
                                                  5 week_ago_5))
GROUP BY table_name, action_item
)
ORDER BY table_name, action_item;
commit;

Select allOpen in new window