?
Solved

UNPIVOT data then PIVOT (over 7 days)

Posted on 2014-10-29
8
Medium Priority
?
108 Views
Last Modified: 2014-11-05
Hello,
I have below code provided by one of your experts to get statistics by weekly for the last 6 weeks. I need this in a daily basis now. How to do that? Please assist

Thank you
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;

Open in new window

0
Comment
Question by:angel7170
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40411233
I think I'm the one that helped you before but I'm not sure what you're asking for this time.

Please provide sample data and expected output.
0
 

Author Comment

by:angel7170
ID: 40411259
Please see attached is the sample data and expected data. Instead of last 6 weeks data, I want last 7 days of data.
SAMPLE-DATA.xls
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40412384
One issue to solve with a PIVOT is the column headings, which you display as:

TABLE_NAME       ACTION _ITEM      29/10/2014       28/10/2014      27/10/2014      26/10/2014      25/10/2014

Those dates in italics present a problem, could they be replaced with constants such as:

TABLE_NAME       ACTION _ITEM      day0       dayminus1      dayminus2      dayminus3      dayminus4 ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40412981
UNPIVOT was just as correct as PIVOT for a question title.

but both are incomplete - because you're doing both.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40414595
Please try this, it is a small adjustment to the query provided in the question, and it does involve BOTH a pivot and an unpivot.

Like that existing query it uses fixed column headings.
SELECT
        table_name AS TABLE_NAME
      , CASE
             WHEN action_item = 'SOURCE_COUNT' THEN 'Source Row Count'
             WHEN action_item = 'TARGET_COUNT' THEN 'Target Row Count'
             WHEN action_item = 'S_MINUS_T_COUNT' THEN 'Source minus Target Count'
             WHEN action_item = 'T_MINUS_S_COUNT' THEN 'Target minus Source Count'
             WHEN action_item = 'BWD_COUNT' THEN 'Both with Differences Count'
        END AS ACTION_ITEM
      , MAX(curr_day)  day_0
      , MAX(day_ago_1) day_1
      , MAX(day_ago_2) day_2
      , MAX(day_ago_3) day_3
      , MAX(day_ago_4) day_4
      , MAX(day_ago_5) day_5
      , MAX(day_ago_6) day_6
FROM (
       SELECT
             run_dt
           , table_name
           , action_item
           , val
           , TRUNC(sysdate) - TRUNC(run_dt) run_day
        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_day IN (  0 curr_day
                                       , 1 day_ago_1
                                       , 2 day_ago_2
                                       , 3 day_ago_3
                                       , 4 day_ago_4
                                       , 5 day_ago_5
                                       , 6 day_ago_6
                                      )
            )
GROUP BY
        table_name
      , action_item
ORDER BY
        table_name
      , action_item

Open in new window

sample result:
|    TABLE_NAME |                 ACTION_ITEM |  DAY_0 |   DAY_1 |   DAY_2 |   DAY_3 |   DAY_4 |   DAY_5 |  DAY_6 |
|---------------|-----------------------------|--------|---------|---------|---------|---------|---------|--------|
|  First Action | Both with Differences Count | (null) |   33235 |   33235 |   33235 |   33235 |   33235 | (null) |
|  First Action |            Source Row Count | (null) | 2551610 | 2551612 | 2551614 | 2551617 | 2551612 | (null) |
|  First Action |   Source minus Target Count | (null) |    5221 |    5221 |    5221 |    5221 |    5221 | (null) |
|  First Action |            Target Row Count | (null) | 2547076 | 2547076 | 2547076 | 2547076 | 2547076 | (null) |
|  First Action |   Target minus Source Count | (null) |     693 |     693 |     693 |     693 |     693 | (null) |
| Second Action | Both with Differences Count | (null) |   67911 |   67911 |   67911 |   67911 |   67911 | (null) |
| Second Action |            Source Row Count | (null) | 1909208 | 1909208 | 1909208 | 1909208 | 1909208 | (null) |
| Second Action |   Source minus Target Count | (null) |       0 |       0 |       0 |       0 |       0 | (null) |
| Second Action |            Target Row Count | (null) | 1892873 | 1892873 | 1892873 | 1892873 | 1892873 | (null) |
| Second Action |   Target minus Source Count | (null) |    1425 |    1425 |    1425 |    1425 |    1425 | (null) |
|  Third Action | Both with Differences Count | (null) |  115542 |  115542 |  115542 |  115542 |  115542 | (null) |
|  Third Action |            Source Row Count | (null) | 2203318 | 2203318 | 2203318 | 2203318 | 2203318 | (null) |
|  Third Action |   Source minus Target Count | (null) |    6105 |    6105 |    6105 |    6105 |    6105 | (null) |
|  Third Action |            Target Row Count | (null) | 2197200 | 2197200 | 2197200 | 2197200 | 2197200 | (null) |
|  Third Action |   Target minus Source Count | (null) |       2 |       2 |       2 |       2 |       2 | (null) |

Open in new window


setup details
**Oracle 11g R2 Schema Setup**:

    CREATE TABLE DQ_HIGH_LEVEL_COUNTS
    	("RUN_DT" timestamp, "TABLE_NAME" varchar2(13), "SOURCE_COUNT" int, "TARGET_COUNT" int, "S_MINUS_T_COUNT" int, "T_MINUS_S_COUNT" int, "BWD_COUNT" int)
    ;
    
    INSERT ALL 
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'First Action', 2551610, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'First Action', 2551612, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'First Action', 2551614, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'First Action', 2551617, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'First Action', 2551612, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    SELECT * FROM dual
    ;

**Query 1**:

    SELECT
            table_name AS TABLE_NAME
          , CASE
                 WHEN action_item = 'SOURCE_COUNT' THEN 'Source Row Count'
                 WHEN action_item = 'TARGET_COUNT' THEN 'Target Row Count'
                 WHEN action_item = 'S_MINUS_T_COUNT' THEN 'Source minus Target Count'
                 WHEN action_item = 'T_MINUS_S_COUNT' THEN 'Target minus Source Count'
                 WHEN action_item = 'BWD_COUNT' THEN 'Both with Differences Count'
            END AS ACTION_ITEM
          , MAX(curr_day)  day_0
          , MAX(day_ago_1) day_1
          , MAX(day_ago_2) day_2
          , MAX(day_ago_3) day_3
          , MAX(day_ago_4) day_4
          , MAX(day_ago_5) day_5
          , MAX(day_ago_6) day_6
    FROM (
           SELECT
                 run_dt
               , table_name
               , action_item
               , val
               , TRUNC(sysdate) - TRUNC(run_dt) run_day
            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_day IN (  0 curr_day
                                           , 1 day_ago_1
                                           , 2 day_ago_2
                                           , 3 day_ago_3
                                           , 4 day_ago_4
                                           , 5 day_ago_5
                                           , 6 day_ago_6
                                          )
                )
    GROUP BY
            table_name
          , action_item
    ORDER BY
            table_name
          , action_item
    

**[Results][2]**:
    
    |    TABLE_NAME |                 ACTION_ITEM |  DAY_0 |   DAY_1 |   DAY_2 |   DAY_3 |   DAY_4 |   DAY_5 |  DAY_6 |
    |---------------|-----------------------------|--------|---------|---------|---------|---------|---------|--------|
    |  First Action | Both with Differences Count | (null) |   33235 |   33235 |   33235 |   33235 |   33235 | (null) |
    |  First Action |            Source Row Count | (null) | 2551610 | 2551612 | 2551614 | 2551617 | 2551612 | (null) |
    |  First Action |   Source minus Target Count | (null) |    5221 |    5221 |    5221 |    5221 |    5221 | (null) |
    |  First Action |            Target Row Count | (null) | 2547076 | 2547076 | 2547076 | 2547076 | 2547076 | (null) |
    |  First Action |   Target minus Source Count | (null) |     693 |     693 |     693 |     693 |     693 | (null) |
    | Second Action | Both with Differences Count | (null) |   67911 |   67911 |   67911 |   67911 |   67911 | (null) |
    | Second Action |            Source Row Count | (null) | 1909208 | 1909208 | 1909208 | 1909208 | 1909208 | (null) |
    | Second Action |   Source minus Target Count | (null) |       0 |       0 |       0 |       0 |       0 | (null) |
    | Second Action |            Target Row Count | (null) | 1892873 | 1892873 | 1892873 | 1892873 | 1892873 | (null) |
    | Second Action |   Target minus Source Count | (null) |    1425 |    1425 |    1425 |    1425 |    1425 | (null) |
    |  Third Action | Both with Differences Count | (null) |  115542 |  115542 |  115542 |  115542 |  115542 | (null) |
    |  Third Action |            Source Row Count | (null) | 2203318 | 2203318 | 2203318 | 2203318 | 2203318 | (null) |
    |  Third Action |   Source minus Target Count | (null) |    6105 |    6105 |    6105 |    6105 |    6105 | (null) |
    |  Third Action |            Target Row Count | (null) | 2197200 | 2197200 | 2197200 | 2197200 | 2197200 | (null) |
    |  Third Action |   Target minus Source Count | (null) |       2 |       2 |       2 |       2 |       2 | (null) |



  [1]: http://sqlfiddle.com/#!4/cf06c/9

  [2]: http://sqlfiddle.com/#!4/cf06c/9/0

Open in new window

0
 

Author Comment

by:angel7170
ID: 40419541
This works but I have one issue is to exclude weekends and it should be just looking for weekdays. I apologize I should have said this previously. I can open another forum if needed. Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question