Forum Stats

  • 3,851,556 Users
  • 2,263,999 Discussions
  • 7,904,778 Comments

Discussions

Categories

Help needed to Pivot value

GuruKumar
GuruKumar Member Posts: 64
edited Apr 19, 2017 1:20AM in SQL & PL/SQL

Hi All,

             I have table like table 1 that has following columns like 'Process' column has  'startprocess' and 'endprocess' two values , in next column 'activity', i have same value in all rows i.e 'Acftivityname' . Last two columns are timestamp datatype has timestamp values .

ProcessActivityActivitystartdateActivityenddate
StartProcessActivityname01/APR/2017 10:30:00 AM01/APR/2017 11:30:00 AM
EndProcessActivityname01/APR/2017 02:30:00 PM01/APR/2017 03:30:00 PM
StartProcessActivityname03/APR/2017 08:30:00 PM03/APR/2017 09:30:00 PM
EndProcessActivityname03/APR/2017 10:30:00 PM03/APR/2017 11:45:00 PM

      From the above available value , i need to display as below, Highlighted in red are to be displayed in single line as below, How to achieve this Kindly help to solve this Thanks in advance .

ActivityActivitystartdateactivityendtime
Activityname01/APR/2017 10:30:00 AM01/APR/2017 03:30:00 PM
Activityname03/APR/2017 08:30:00 PM03/APR/2017 11:45:00 PM

Regards,

Guru

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • GuruKumar
    GuruKumar Member Posts: 64
    edited Apr 13, 2017 2:30AM Answer ✓

    Hi  All,

    i have achieved it fro below analytical query,

    select * from (

    SELECT acin_id, acti_name, prce_display_name, datetime_started,      

           LAST_VALUE(datetime_ended) OVER (ORDER BY acin_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_val

    FROM   xc)

    where acti_name <> 'END'

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Apr 12, 2017 12:39PM

    Hi,

     

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

    See the forum FAQ: 

    GuruKumar wrote:Hi All, I have table like table 1 that has following columns like 'Process' column has 'startprocess' and 'endprocess' two values , in next column 'activity', i have same value in all rows i.e 'Acftivityname' . Last two columns are timestamp datatype has timestamp values .ProcessActivityActivitystartdateActivityenddateStartProcessActivityname01/APR/2017 10:30:00 AM01/APR/2017 11:30:00 AMEndProcessActivityname01/APR/2017 02:30:00 PM01/APR/2017 03:30:00 PMStartProcessActivityname03/APR/2017 08:30:00 PM03/APR/2017 09:30:00 PMEndProcessActivityname03/APR/2017 10:30:00 PM03/APR/2017 11:45:00 PM From the above available value , i need to display as below, Highlighted in red are to be displayed in single line as below, How to achieve this Kindly help to solve this Thanks in advance .ActivityActivitystartdateactivityendtimeActivityname01/APR/2017 10:30:00 AM01/APR/2017 03:30:00 PMActivityname03/APR/2017 08:30:00 PM03/APR/2017 11:45:00 PMRegards,Guru

    What if the first row (when sorting by activitystartdate) has process='EndProcess'?

    What if there are 2 (or more) consectutive rows with the same value for process?

    What if there are unequal numbers of 'StartProcess' and 'EndProcess' rows?

    If these things, or other special cases, are possible, then include examples when you post the sample data and desired results.

    · Share on TwitterShare on Facebook
  • GuruKumar
    GuruKumar Member Posts: 64
    edited Apr 12, 2017 1:17PM

    Hi Frank ,

                   thanks for the response ,

    What if the first row (when sorting by activitystartdate) has process='EndProcess'?

    => Table has unique id , and i am sorting it by ascending order. by activitystartdate column will not be sorted

    What if there are 2 (or more) consectutive rows with the same value for process?

    =>  in 'process' column i have only two values like 'STARTPROCESS' and 'ENDPROCESS'

    , other values will not come.

    What if there are unequal numbers of 'StartProcess' and 'EndProcess' rows?

    => there will not be any unequal number of rows , if there is start the nsurely there will be end process with end time.

    If these things, or other special cases, are possible, then include examples when you post the sample data and desired results.

    =>  Below table is the actual data :

    pastedImage_1.png

    Desired date will be like below :

    Highlighted in color from two different process 'START' and 'END' should have the timestamp value in same row as below , like that for other ID's as well.

    pastedImage_2.png

    Regards,

    Guru

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Apr 12, 2017 4:39PM

    HI,

    GuruKumar wrote:Hi Frank , thanks for the response , What if the first row (when sorting by activitystartdate) has process='EndProcess'?=> Table has unique id , and i am sorting it by ascending order. by activitystartdate column will not be sorted

    I see; it's id that determines what terms like "first" and "consecutive" mean.

    What if the order of the ids does not match activitystartdate?  Will the activitystartdate in the output be the earliest activitystartdate, or the one that is related to the lowest id?

    Regardless of how the data is sorted, what do you want when the first row has process = 'EndProcess'?

    What if there are 2 (or more) consectutive rows with the same value for process?=> in 'process' column i have only two values like 'STARTPROCESS' and 'ENDPROCESS', other values will not come.

    You used 6 different values for process in message #2 alone.  If the value is 'Start', then don't say it's 'start' or 'STARTPROCESS'.

    However, that's beside the point.  The question is: What happens when you have several consecutive rows with the same value for process?  For example, what if the 1st row has 'STARTPROCESS' and the 2nd row has 'STARTPROCESS' also?

    What if there are unequal numbers of 'StartProcess' and 'EndProcess' rows?

    => there will not be any unequal number of rows , if there is start the nsurely there will be end process with end time.

    If these things, or other special cases, are possible, then include examples when you post the sample data and desired results.=> Below table is the actual data :
    pastedImage_1.png

    Desired date will be like below :

    Highlighted in color from two different process 'START' and 'END' should have the timestamp value in same row as below , like that for other ID's as well.

    pastedImage_2.png

    Regards,

    Guru

    Once again, post CREATE TABLE and INSERT statements (or equivalent) for the sample data.

    Pivoting occurs when you have 1 column on several rows, and you display it as several columns on 1 row;  that is, you're reducing the number of rows and incresing the number of columns..  In this problem, we want to decrease the number of rows, but we don't want any change in the number of columns, so it isn't really a pivot problem.  This is more of a GROUP BY problem, or a MATCH_RECOGNIZE ... ONE ROW PER MATCH problem.   MATCH_RECOGNIZE only works in Oracle 12, so it's especially important for you to say which version of Oracle you have.

    · Share on TwitterShare on Facebook
  • GuruKumar
    GuruKumar Member Posts: 64
    edited Apr 13, 2017 2:08AM

    Hi Frank,

                  I am using Oracle 11g  Release 2 ,  Giving sample data and desired out put below ,

    create table xc

    (

    ACIN_ID NUMBER(38,0),

    ACTI_NAME VARCHAR2(100 CHAR),

    PRCE_DISPLAY_NAME VARCHAR2(100 CHAR),

    DATETIME_STARTED TIMESTAMP(3) WITH TIME ZONE,

    DATETIME_ENDED TIMESTAMP(3) WITH TIME ZONE,

    PARENT_PRIN_ID NUMBER)

    /

    SET DEFINE OFF;

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148252224,'START','CREATE_SUPER_CUBE',to_timestamp_tz('04-APR-17 05.11.14.362000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('04-APR-17 05.11.14.409000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19244416);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148258314,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('04-APR-17 05.49.43.620000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('04-APR-17 05.49.43.771000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148463169,'START','CREATE_SUPER_CUBE',to_timestamp_tz('05-APR-17 04.33.48.793000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('05-APR-17 04.33.48.820000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19270621);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148467411,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('05-APR-17 05.06.38.479000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('05-APR-17 05.06.38.586000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148832127,'START','CREATE_SUPER_CUBE',to_timestamp_tz('06-APR-17 06.08.52.698000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('06-APR-17 06.08.52.746000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19316365);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (148848708,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('06-APR-17 07.06.31.993000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('06-APR-17 07.06.32.024000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149173419,'START','CREATE_SUPER_CUBE',to_timestamp_tz('07-APR-17 10.38.03.873000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('07-APR-17 10.38.03.955000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19358528);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149178204,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('07-APR-17 11.15.18.719000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('07-APR-17 11.15.18.742000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149658705,'START','CREATE_SUPER_CUBE',to_timestamp_tz('11-APR-17 08.47.43.634000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('11-APR-17 08.47.43.669000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19417476);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149670559,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('11-APR-17 09.52.00.585000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('11-APR-17 09.52.00.621000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149846759,'START','CREATE_SUPER_CUBE',to_timestamp_tz('12-APR-17 07.57.44.649000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('12-APR-17 07.57.44.770000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),19440936);

    Insert into xc (ACIN_ID,ACTI_NAME,PRCE_DISPLAY_NAME,DATETIME_STARTED,DATETIME_ENDED,PARENT_PRIN_ID) values (149863141,'END','SUPERCUBE_DATA_DELIVERY',to_timestamp_tz('12-APR-17 09.07.24.467000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),to_timestamp_tz('12-APR-17 09.07.24.778000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'),null);

     

    ACIN_idACTI_NAMEPRCE_DISPLAY_NAMEDATETIME_STARTEDDATETIME_ENDEDPARENT_PRIN_ID
    148252224STARTCREATE_SUPER_CUBE04-APR-17 05.11.14.362000000 AM -05:0004-APR-17 05.11.14.409000000 AM -05:0019244416
    148258314ENDSUPERCUBE_DATA_DELIVERY04-APR-17 05.49.43.620000000 AM -05:0004-APR-17 05.49.43.771000000 AM -05:00
    148463169STARTCREATE_SUPER_CUBE05-APR-17 04.33.48.793000000 AM -05:0005-APR-17 04.33.48.820000000 AM -05:0019270621
    148467411ENDSUPERCUBE_DATA_DELIVERY05-APR-17 05.06.38.479000000 AM -05:0005-APR-17 05.06.38.586000000 AM -05:00
    148832127STARTCREATE_SUPER_CUBE06-APR-17 06.08.52.698000000 AM -05:0006-APR-17 06.08.52.746000000 AM -05:0019316365
    148848708ENDSUPERCUBE_DATA_DELIVERY06-APR-17 07.06.31.993000000 AM -05:0006-APR-17 07.06.32.024000000 AM -05:00
    149173419STARTCREATE_SUPER_CUBE07-APR-17 10.38.03.873000000 AM -05:0007-APR-17 10.38.03.955000000 AM -05:0019358528
    149178204ENDSUPERCUBE_DATA_DELIVERY07-APR-17 11.15.18.719000000 AM -05:0007-APR-17 11.15.18.742000000 AM -05:00
    149658705STARTCREATE_SUPER_CUBE11-APR-17 08.47.43.634000000 AM -05:0011-APR-17 08.47.43.669000000 AM -05:0019417476
    149670559ENDSUPERCUBE_DATA_DELIVERY11-APR-17 09.52.00.585000000 AM -05:0011-APR-17 09.52.00.621000000 AM -05:00
    149846759STARTCREATE_SUPER_CUBE12-APR-17 07.57.44.649000000 AM -05:0012-APR-17 07.57.44.770000000 AM -05:0019440936
    149863141ENDSUPERCUBE_DATA_DELIVERY12-APR-17 09.07.24.467000000 AM -05:00

    12-APR-17 09.07.24.778000000 AM -05:00

    Desired Output would be :

     

    ACIN_idACTI_NAMEPRCE_DISPLAY_NAMEDATETIME_STARTEDDATETIME_ENDEDPARENT_PRIN_ID
    148252224STARTCREATE_SUPER_CUBE04-APR-17 05.11.14.362000000 AM -05:0004-APR-17 05.49.43.771000000 AM -05:0019244416
    148463169STARTCREATE_SUPER_CUBE05-APR-17 04.33.48.793000000 AM -05:0005-APR-17 05.06.38.586000000 AM -05:0019270621
    148832127STARTCREATE_SUPER_CUBE06-APR-17 06.08.52.698000000 AM -05:0006-APR-17 07.06.32.024000000 AM -05:0019316365
    149173419STARTCREATE_SUPER_CUBE07-APR-17 10.38.03.873000000 AM -05:0007-APR-17 11.15.18.742000000 AM -05:0019358528
    149658705STARTCREATE_SUPER_CUBE11-APR-17 08.47.43.634000000 AM -05:0011-APR-17 09.52.00.621000000 AM -05:0019417476
    149846759STARTCREATE_SUPER_CUBE12-APR-17 07.57.44.649000000 AM -05:0012-APR-17 09.07.24.778000000 AM -05:0019440936
    · Share on TwitterShare on Facebook
  • GuruKumar
    GuruKumar Member Posts: 64
    edited Apr 13, 2017 2:30AM Answer ✓

    Hi  All,

    i have achieved it fro below analytical query,

    select * from (

    SELECT acin_id, acti_name, prce_display_name, datetime_started,      

           LAST_VALUE(datetime_ended) OVER (ORDER BY acin_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_val

    FROM   xc)

    where acti_name <> 'END'

    · Share on TwitterShare on Facebook
  • Srinivas Vanahalli
    Srinivas Vanahalli Member Posts: 81
    edited Apr 13, 2017 2:30AM

    Hi try with below.. this works well only if Ids are sorted in ascending order in your data.

    --alter session set nls_date_format='dd-mon-yyyy hh12:mi:ss am'

    with data as

    (

    select 1 as id,'Start' as Process,'Acti' as activity,to_date('01/MAR/2017 9:30:00 am','dd/mon/yyyy hh12:mi:ss pm') as start_time,

    to_date('01/MAR/2017 11:30:00 am','dd/mon/yyyy hh12:mi:ss pm') as end_time

    from dual

    union all

    select 4 as id,'End' as Process,'Acti' as activity,to_date('01/MAR/2017 1:30:00 pm','dd/mon/yyyy hh12:mi:ss pm') as start_time,

    to_date('01/MAR/2017 2:30:00 pm','dd/mon/yyyy hh12:mi:ss pm') as end_time

    from dual

    union all

    select 8 as id,'Start' as Process,'Acti' as activity,to_date('01/MAR/2017 9:30:00 pm','dd/mon/yyyy hh12:mi:ss pm') as start_time,

    to_date('01/MAR/2017 10:30:00 pm','dd/mon/yyyy hh12:mi:ss pm') as end_time

    from dual

    union all

    select 11 as id,'End' as Process,'Acti' as activity,to_date('02/MAR/2017 11:30:00 am','dd/mon/yyyy hh12:mi:ss pm') as start_time,

    to_date('02/MAR/2017 2:30:00 pm','dd/mon/yyyy hh12:mi:ss pm') as end_time

    from dual

    )

    select id,process,activity,st as start_time,end_time

    from

    (

    select last_value(case when process='Start' then start_time end ignore nulls) over(order by id) as st,d.* from data d

    ) where process='End';

    · Share on TwitterShare on Facebook
  • Rocky
    Rocky Member Posts: 196 Bronze Badge
    edited Apr 19, 2017 1:20AM

    Hi User,

    if sometimes near the future u will migrate to 12c then below query will fulfill your requirement but as of now its of no use in your case and posting it just for reference for others those are on 12c can get an idea -

    SELECT *

        FROM xc MATCH_RECOGNIZE

        (ORDER BY acin_id

          MEASURES

           first(starting.acin_id) as acin_id,

           first(starting.acti_name) as acin_name,

           first(starting.prce_display_name) as prce_display_name,

           first(starting.datetime_started) as start_time,

           last(ending.datetime_ended) as end_time,

           first(starting.parent_prin_id) as parent_prin_id

          ONE ROW PER MATCH

    pattern( starting  ending)

    define

    starting as acti_name  = 'START',

    ending as  acti_name  = 'END'

    )

    · Share on TwitterShare on Facebook
This discussion has been closed.