Forum Stats

  • 3,853,805 Users
  • 2,264,277 Discussions
  • 7,905,450 Comments

Discussions

pivot multiples on single table

Gmoney
Gmoney Member Posts: 148
edited Mar 14, 2014 12:52PM in SQL & PL/SQL


Good day all.

I am working on using multiple PIVOTS on the same table. I am not sure it can be done, but I would like to attempt.

Working with 11g.R2.


CREATE TABLE TSK_CHK
   (ID   VARCHAR2 (11),
TASK  VARCHAR2 (11),
TASK_DESC VARCHAR2 (11),
TASK_DATE DATE,
JEOP_CODE VARCHAR2 (4),
JEOP_DESC VARCHAR (11))
INSERT INTO TSK_CHK VALUES ('ABC/506208','OSP', 'RUNG', to_date('03/10/2014 21:00:00', 'dd/mm/yyyy hh24:mi:ss'), '01Z', 'GOOD');
INSERT INTO TSK_CHK VALUES ('ABC/506208','RTD', 'JUMPG', to_date('03/10/2014 21:15:00', 'dd/mm/yyyy hh24:mi:ss'),'23A', 'BAD');
INSERT INTO TSK_CHK VALUES ('ABC/506208','RVW', 'LOOKG', to_date('03/10/2014 21:30:00', 'dd/mm/yyyy hh24:mi:ss'),'34B', 'UGLY');
INSERT INTO TSK_CHK VALUES ('ABC/506208','ING', 'ROLLG', to_date('03/10/2014 21:45:00', 'dd/mm/yyyy hh24:mi:ss'),'56C','FUN');
INSERT INTO TSK_CHK VALUES ('XYZ/802605','MP3', 'STOPG', to_date('03/10/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'),'78D','SCARY');

What I am trying to achieve initially is a result like this:

As you can see there is also a need to PIVOT the JEOP_CODE and JEAOP_DESC as well.

IDTASK_1TASK_DESC_1TASK_DATE_1TASK_2TASK_DESC_2TASK_DATE_2TASK_3TASK_DESC_3TASK_DATE_3TASK_4TASK_DESC_4TASK_DATE_4TASK_5TASK_DESC_5TASK_DATE_5JEOP_CODE_1JEOP_DESC_1JEOP_CODE_2JEOP_DESC_2JEOP_CODE_3JEOP_DESC_3JEOP_CODE_4JEOP_DESC_4JEOP_CODE_5JEOP_DESC_5
ABC/506208OSPRUNG03/10/2014 21:00:00RTDJUMPG03/10/2014 21:15:00RVWLOOKG03/10/2014 21:30:00INGROLLG03/10/2014 21:45:0001ZGOOD23ABAD34BUGLY56CFUN
XYZ/802605MP3STOPG03/10/2014 22:00:0078DSCARY

Here is where I have started with the SQL:

select *
from (
select ID, TASK,
ROW_NUMBER () OVER
      (PARTITION BY ID
      ORDER BY ROWID) rn1
from TSK_CHK
PIVOT
      (MAX(TASK)
      For rn1 IN
      (1 AS "TASK_1",
      2 AS "TASK_2",
      3 AS"TASK_3",
      4 AS "TASK_4",
      5 AS "TASK_5"))

This will given me the basic PIVOT of the TASK but from there I am not sure how to add the other fields.

As always the time and assitance is appreciated.

Regards,

G

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited Mar 11, 2014 5:45PM Answer ✓

    SELECT  TASK_1_TASK TASK_1,

            TASK_1_DESC TASK_DESC_1,

            TASK_1_DATE TASK_DATE_1,

            TASK_2_TASK TASK_2,

            TASK_2_DESC TASK_DESC_2,

            TASK_2_DATE TASK_DATE_2,

            TASK_3_TASK TASK_3,

            TASK_3_DESC TASK_DESC_3,

            TASK_3_DATE TASK_DATE_3,

            TASK_4_TASK TASK_4,

            TASK_4_DESC TASK_DESC_4,

            TASK_4_DATE TASK_DATE_4,

            TASK_5_TASK TASK_5,

            TASK_5_DESC TASK_DESC_5,

            TASK_5_DATE TASK_DATE_5,

            TASK_1_JEOP_CODE JEOP_CODE_1,

            TASK_1_JEOP_DESC JEOP_DESC_1,

            TASK_2_JEOP_CODE JEOP_CODE_2,

            TASK_2_JEOP_DESC JEOP_DESC_2,

            TASK_3_JEOP_CODE JEOP_CODE_3,

            TASK_3_JEOP_DESC JEOP_DESC_3,

            TASK_4_JEOP_CODE JEOP_CODE_4,

            TASK_4_JEOP_DESC JEOP_DESC_4,

            TASK_5_JEOP_CODE JEOP_CODE_5,

            TASK_5_JEOP_DESC JEOP_DESC_5

      FROM  (

             SELECT  T.*,

                     ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID) RN

               FROM  TSK_CHK T

            )

      PIVOT(

            MAX(TASK) TASK,

            MAX(TASK_DESC) "DESC",

            MAX(TASK_DATE) "DATE",

            MAX(JEOP_CODE) "JEOP_CODE",

            MAX(JEOP_DESC) "JEOP_DESC"

            FOR RN IN (

                       1 AS "TASK_1",

                       2 AS "TASK_2",

                       3 AS "TASK_3",

                       4 AS "TASK_4",

                       5 AS "TASK_5"

                      )

           )

    /


    TASK_1 TASK_DESC_1 TASK_DATE_1         TASK_2 TASK_DESC_2 TASK_DATE_2         TASK_3 TASK_DESC_3 TASK_DATE_3         TASK_4 TASK_DESC_4 TASK_DATE_4         TASK_5 TASK_DESC_5 TASK_DATE_5         JEOP JEOP_DESC_1 JEOP JEOP_DESC_2 JEOP JEOP_DESC_3 JEOP JEOP_DESC_4 JEOP JEOP_DESC_5
    ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ---- ----------- ---- ----------- ---- ----------- ---- ----------- ---- -----------
    OSP    RUNG        10/03/2014 21:00:00 RTD    JUMPG       10/03/2014 21:15:00 RVW    LOOKG       10/03/2014 21:30:00 ING    ROLLG       10/03/2014 21:45:00                                        01Z  GOOD        23A  BAD         34B  UGLY        56C  FUN
    MP3    STOPG       10/03/2014 22:00:00                                                                                                                                                             78D  SCARY

    SQL>


    SY.

Answers

  • JonWat
    JonWat Member Posts: 552 Silver Badge

    select * 

    from ( 

    select ID, TASK,  task_desc, task_date,jeop_code,jeop_desc,

    ROW_NUMBER () OVER 

          (PARTITION BY ID 

          ORDER BY ROWID) rn1 

    from TSK_CHK  )

    PIVOT 

          (MAX(TASK) as task, max(task_desc) as tdesc , max(task_date) tdate ,max(jeop_code) jeop_code, max(jeop_desc) jeop_desc

          For rn1 IN 

          (1 AS TASK_1, 

          2 AS TASK_2, 

          3 AS TASK_3, 

          4 AS TASK_4, 

          5 AS TASK_5));

    Gets you part of the way. But where you want to have both the  JEOP items separate , you might have to do three separate pivot queries and then join them together

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,497 Red Diamond
    edited Mar 11, 2014 3:53PM

    Hi,

    GMoney wrote:
    
    
    Good day all. I am working on using multiple PIVOTS on the same table. I am not sure it can be done, but I would like to attempt. Working with 11g.R2.
    1. <SPAN><SPAN><SPAN><SPAN><SPAN><SPAN>  
    2. CREATE TABLE TSK_CHK  
    3.    (ID   VARCHAR2 (11),  
    4. TASK  VARCHAR2 (11),  
    5. TASK_DESC VARCHAR2 (11),  
    6. TASK_DATE DATE,  
    7. JEOP_CODE VARCHAR2 (4),  
    8. JEOP_DESC VARCHAR (11))  
    9.    
    10. INSERT INTO TSK_CHK VALUES ('ABC/506208','OSP', 'RUNG', to_date('03/10/2014 21:00:00', 'dd/mm/yyyy hh24:mi:ss'), '01Z', 'GOOD');  
    11. INSERT INTO TSK_CHK VALUES ('ABC/506208','RTD', 'JUMPG', to_date('03/10/2014 21:15:00', 'dd/mm/yyyy hh24:mi:ss'),'23A', 'BAD');  
    12. INSERT INTO TSK_CHK VALUES ('ABC/506208','RVW', 'LOOKG', to_date('03/10/2014 21:30:00', 'dd/mm/yyyy hh24:mi:ss'),'34B', 'UGLY');  
    13. INSERT INTO TSK_CHK VALUES ('ABC/506208','ING', 'ROLLG', to_date('03/10/2014 21:45:00', 'dd/mm/yyyy hh24:mi:ss'),'56C','FUN');  
    14. INSERT INTO TSK_CHK VALUES ('XYZ/802605','MP3', 'STOPG', to_date('03/10/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'),'78D','SCARY');</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN> 
     CREATE TABLE TSK_CHK    (ID   VARCHAR2 (11), TASK  VARCHAR2 (11), TASK_DESC VARCHAR2 (11), TASK_DATE DATE, JEOP_CODE VARCHAR2 (4), JEOP_DESC VARCHAR (11)) INSERT INTO TSK_CHK VALUES ('ABC/506208','OSP', 'RUNG', to_date('03/10/2014 21:00:00', 'dd/mm/yyyy hh24:mi:ss'), '01Z', 'GOOD'); INSERT INTO TSK_CHK VALUES ('ABC/506208','RTD', 'JUMPG', to_date('03/10/2014 21:15:00', 'dd/mm/yyyy hh24:mi:ss'),'23A', 'BAD'); INSERT INTO TSK_CHK VALUES ('ABC/506208','RVW', 'LOOKG', to_date('03/10/2014 21:30:00', 'dd/mm/yyyy hh24:mi:ss'),'34B', 'UGLY'); INSERT INTO TSK_CHK VALUES ('ABC/506208','ING', 'ROLLG', to_date('03/10/2014 21:45:00', 'dd/mm/yyyy hh24:mi:ss'),'56C','FUN'); INSERT INTO TSK_CHK VALUES ('XYZ/802605','MP3', 'STOPG', to_date('03/10/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'),'78D','SCARY');
    
    What I am trying to achieve initially is a result like this:
    As you can see there is also a need to PIVOT the JEOP_CODE and JEAOP_DESC as well.
    
    
    
    IDTASK_1TASK_DESC_1TASK_DATE_1TASK_2TASK_DESC_2TASK_DATE_2TASK_3TASK_DESC_3TASK_DATE_3TASK_4TASK_DESC_4TASK_DATE_4TASK_5TASK_DESC_5TASK_DATE_5JEOP_CODE_1JEOP_DESC_1JEOP_CODE_2JEOP_DESC_2JEOP_CODE_3JEOP_DESC_3JEOP_CODE_4JEOP_DESC_4JEOP_CODE_5JEOP_DESC_5
    
    
    
    ABC/506208
    OSP
    RUNG
    03/10/2014 21:00:00
    RTD
    JUMPG
    03/10/2014 21:15:00
    RVW
    LOOKG
    03/10/2014 21:30:00
    ING
    ROLLG
    03/10/2014 21:45:00
    
    
    
    01Z
    GOOD
    23A
    BAD
    34B
    UGLY
    56C
    FUN
    
    
    
    
    XYZ/802605
    MP3
    STOPG
    03/10/2014 22:00:00
    
    
    
    
    
    
    
    
    
    
    
    
    78D
    SCARY
    
    
    
    
    
    
    
    
    
    
    
    
    Here is where I have started with the SQL:
    
    
     
    1. <SPAN><SPAN><SPAN><SPAN><SPAN><SPAN>  
    2. select *  
    3. from (  
    4. select ID, TASK,  
    5. ROW_NUMBER () OVER  
    6.       (PARTITION BY ID  
    7.       ORDER BY ROWID) rn1  
    8. from TSK_CHK  
    9. PIVOT  
    10.       (MAX(TASK)  
    11.       For rn1 IN 
    12.       (1 AS "TASK_1",  
    13.       2 AS "TASK_2",  
    14.       3 AS"TASK_3",  
    15.       4 AS "TASK_4",  
    16.       5 AS "TASK_5"))</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN> 
     select * from ( select ID, TASK, ROW_NUMBER () OVER       (PARTITION BY ID       ORDER BY ROWID) rn1 from TSK_CHK PIVOT       (MAX(TASK)       For rn1 IN       (1 AS "TASK_1",       2 AS "TASK_2",       3 AS"TASK_3",       4 AS "TASK_4",       5 AS "TASK_5"))
    This will given me the basic PIVOT of the TASK but from there I am not sure how to add the other fields.
    
    As always the time and assitance is appreciated.
    
    Regards,
    
    G
     

    Are you sure that's the query that pivots 1 column correctly?  It has unblanaced parentheses.  If I fix that, I get ORA-00904" "RN1": invlaid identifier.

    Anyhow, here's one way to pivot multiple columns:

    WITH    got_rn1    AS
    (
        SELECT  id, task, task_desc, task_date
        ,       ROW_NUMBER () OVER ( PARTITION BY  id
                                     ORDER BY      NULL   -- or ROWID
                                   )  AS rn1
        FROM    tsk_chk
    )
    SELECT  *
    FROM    got_rn1
    PIVOT   (    MAX (task)       AS task
            ,    MAX (task_desc)  AS task_desc
            ,    MAX (task_date)  AS task_date
            FOR  rn1  IN  ( 1  AS "1ST"
                          , 2  AS "2ND"
                          , 3  AS "3RD"
                          , 4  AS "4TH"
                          , 5  AS "5TH"
                          )
            )
    ;
     

    PIVOT automatically creates names with the part derived from the column (e.g. "1ST", "2ND", ... above) first, and the part that indicates the function (e.g. "TASK", "TASK_DESC", ...) last, so you get output columns like "1ST_TASK", "1ST_TASK_DESC", ..., "5TH_TASK_DATE".  If you really want names like "TASK_1", "TASK_DESC_1", ... then you can give them aliases in the main query.  That is, instead of just

    SELECT  *
     

    in the main query, say,

    SELECT  id
    ,       "1ST_TASK"        AS task_1
    ,       "1ST_TASK_DESC"   AS task_desc_1
    ,       ...
     

    This is also how to display the columns in any order you want them.

    There may be an easier, more elegant way, but I don't know it.

    I'm not sure what you were saying about JEOP_CODE and JEOP_DESC.  I don't see corresponding columns in the desired output, but it looks like my browser isn't showing everything you posted..  If you do want to include them, then include them in the sub-query got_rn1, and put another couple of lines at the beginning of the PIVOT clause.

    Frank Kulash
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited Mar 11, 2014 5:45PM Answer ✓

    SELECT  TASK_1_TASK TASK_1,

            TASK_1_DESC TASK_DESC_1,

            TASK_1_DATE TASK_DATE_1,

            TASK_2_TASK TASK_2,

            TASK_2_DESC TASK_DESC_2,

            TASK_2_DATE TASK_DATE_2,

            TASK_3_TASK TASK_3,

            TASK_3_DESC TASK_DESC_3,

            TASK_3_DATE TASK_DATE_3,

            TASK_4_TASK TASK_4,

            TASK_4_DESC TASK_DESC_4,

            TASK_4_DATE TASK_DATE_4,

            TASK_5_TASK TASK_5,

            TASK_5_DESC TASK_DESC_5,

            TASK_5_DATE TASK_DATE_5,

            TASK_1_JEOP_CODE JEOP_CODE_1,

            TASK_1_JEOP_DESC JEOP_DESC_1,

            TASK_2_JEOP_CODE JEOP_CODE_2,

            TASK_2_JEOP_DESC JEOP_DESC_2,

            TASK_3_JEOP_CODE JEOP_CODE_3,

            TASK_3_JEOP_DESC JEOP_DESC_3,

            TASK_4_JEOP_CODE JEOP_CODE_4,

            TASK_4_JEOP_DESC JEOP_DESC_4,

            TASK_5_JEOP_CODE JEOP_CODE_5,

            TASK_5_JEOP_DESC JEOP_DESC_5

      FROM  (

             SELECT  T.*,

                     ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID) RN

               FROM  TSK_CHK T

            )

      PIVOT(

            MAX(TASK) TASK,

            MAX(TASK_DESC) "DESC",

            MAX(TASK_DATE) "DATE",

            MAX(JEOP_CODE) "JEOP_CODE",

            MAX(JEOP_DESC) "JEOP_DESC"

            FOR RN IN (

                       1 AS "TASK_1",

                       2 AS "TASK_2",

                       3 AS "TASK_3",

                       4 AS "TASK_4",

                       5 AS "TASK_5"

                      )

           )

    /


    TASK_1 TASK_DESC_1 TASK_DATE_1         TASK_2 TASK_DESC_2 TASK_DATE_2         TASK_3 TASK_DESC_3 TASK_DATE_3         TASK_4 TASK_DESC_4 TASK_DATE_4         TASK_5 TASK_DESC_5 TASK_DATE_5         JEOP JEOP_DESC_1 JEOP JEOP_DESC_2 JEOP JEOP_DESC_3 JEOP JEOP_DESC_4 JEOP JEOP_DESC_5
    ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ------ ----------- ------------------- ---- ----------- ---- ----------- ---- ----------- ---- ----------- ---- -----------
    OSP    RUNG        10/03/2014 21:00:00 RTD    JUMPG       10/03/2014 21:15:00 RVW    LOOKG       10/03/2014 21:30:00 ING    ROLLG       10/03/2014 21:45:00                                        01Z  GOOD        23A  BAD         34B  UGLY        56C  FUN
    MP3    STOPG       10/03/2014 22:00:00                                                                                                                                                             78D  SCARY

    SQL>


    SY.

  • Gmoney
    Gmoney Member Posts: 148

    Frank - thanks for your response. It was most helpful. I appreciate your time as always.

    G

  • Gmoney
    Gmoney Member Posts: 148

    SY - worked exactly as needed. Both you and frank were a great help as always. Thank you

    G

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    GMoney wrote:
    
    worked exactly as needed. 
    

    Well, I have some reservations about that. ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID) will result in:

    ID

    TASK

    RN

    'ABC/506208'

    'OSP'

    1

    'ABC/506208'

    'RTD'

    2

    'ABC/506208'

    'RVW'

    3

    'ABC/506208'

    'ING'

    4

    'XYZ/802605'

    'MP3'

    1

    So PIVOT will aggregate ID='ABC/506208' TASK='OSP' and ID='XYZ/802605' TASK='MP3' which doesn't seem right. But the worst part is your RN is non-deterministic. If, for example, tomorrow you will insert a new row for ID='ABC/506208' with TASK='whatever' and extent Oracle finds for that insert is such that ROWID for new row is < than ROWID for ID='ABC/506208' TASK='OSP' your query will produce different results. It will aggregate ID='ABC/506208' TASK='whatever' and ID='XYZ/802605' TASK='MP3'.

    SY.

  • Gmoney
    Gmoney Member Posts: 148

    Well I am trying to track these task changes. Basically, when a task completes I would not want it to appear anymore in my results. In this case a new task would replace a existing task, and when there are no task then they are ignored as part of my larger query.

    Trying to test what you brought up now.

    G

  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    edited Mar 14, 2014 12:54PM

    I'd suggest using the following for the RN column:

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TASK_DATE, ROWID) RN
    

    Instead of

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID) RN
    

    that way the column ordering will be more deterministic.  Additionally you could add additional columns to the order by to serve as deterministic tie breakers when the if the task_date is the same.

This discussion has been closed.