Skip to Main Content

Japanese

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dynamic Pivot Help

PS SPAug 17 2017

Hello Together,

     Table1               

PK    1    APP1           

PK    2    APP2           

PK    4    APP3           

PK    8    APP4           

    Table2               

    MEMBER_ID    Used_in           

FK(OBJECT_ID)    1    1           

FK(OBJECT_ID)    2    3           

FK(OBJECT_ID)    3    5           

FK(OBJECT_ID)    4    15           

FK(OBJECT_ID)    5    12           

    Table3               

    Object_ID    Member Name           

PK    1    MEMBER_1           

PK    2    MEMBER_2           

PK    3    MEMBER_3           

PK    4    MEMBER_4           

PK    5    MEMBER_5           

    Desired Out put               

Object_ID    Member Name    VALID FOR APP1    VALID FOR APP2    VALID FOR APP3    VALID For APP4

1    MEMBER_1    TRUE    FALSE    FALSE    FALSE

2    MEMBER_2    TRUE    TRUE    FALSE    FALSE

3    MEMBER_3    FALSE    TRUE    TRUE    FALSE

4    MEMBER_4    TRUE    TRUE    TRUE    TRUE

5    MEMBER_5    FALSE    FALSE    TRUE    TRUE

Desired out put comes, based on the combinations of Table1 ID's : For Example 15 = sum of all Table1 ID's and 12 = 8 + 4 (hence, it's valid for App3 and App4) which are App3 and App4.

Above Relations as well i have mentioned. PK -- Primary Key, FK -- Foriegn Key.

Thanks

CREATE TABLE "CT_TABLE3"

  (

    "OBJECT_ID"  NUMBER(*,0),

    "MEMBERNAME" VARCHAR2(100 BYTE)

  )

  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

  (

    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

  )

  TABLESPACE "USERS" ;

  CREATE TABLE "CT_TABLE2"

  (

    "MEMBER_ID" NUMBER(*,0),

    "USEDIN"    VARCHAR2(100 BYTE)

  )

  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

  (

    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

  )

  TABLESPACE "USERS" ;

  CREATE TABLE "CT_TABLE3"

  (

    "OBJECT_ID"  NUMBER(*,0),

    "MEMBERNAME" VARCHAR2(100 BYTE)

  )

  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

  (

    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

  )

  TABLESPACE "USERS" ;

INSERT INTO "CT_TABLE1" (ID, APP) VALUES ('1', 'App1')

INSERT INTO "CT_TABLE1" (ID, APP) VALUES ('2', 'App2')

INSERT INTO "CT_TABLE1" (ID, APP) VALUES ('4', 'App3')

INSERT INTO "CT_TABLE1" (ID, APP) VALUES ('8', 'App4')

Commit Successful

INSERT INTO "CT_TABLE2" (MEMBER_ID, USEDIN) VALUES ('1', '1')

INSERT INTO "CT_TABLE2" (MEMBER_ID, USEDIN) VALUES ('2', '3')

INSERT INTO "CT_TABLE2" (MEMBER_ID, USEDIN) VALUES ('3', '5')

INSERT INTO "CT_TABLE2" (MEMBER_ID, USEDIN) VALUES ('4', '15')

INSERT INTO "CT_TABLE2" (MEMBER_ID, USEDIN) VALUES ('5', '12')

Commit Successful

INSERT INTO "CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ('1', 'MEMBER_1')

INSERT INTO "CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ('2', 'MEMBER_2')

INSERT INTO "CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ('3', 'MEMBER_3')

INSERT INTO "CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ('4', 'MEMBER_4')

INSERT INTO "CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ('5', 'MEMBER_5')

Commit Successful

I tried this query for my desired output  result has come but its not dynamic.

  WITH    data_to_pivot    AS

    (

    SELECT  t3.object_id, t3.membername

       ,       APP

       ,       CASE

                   WHEN  t1.ID = BITAND (t1.ID, t2.usedin)

                   THEN  'FALSE'

                  ELSE  'TRUE'

               END   AS t_f

        FROM        CT_TABLE1  t1

        CROSS JOIN  CT_TABLE2  t2

        JOIN        CT_TABLE3  t3  ON  t3.object_id  = t2.member_id

         )

    SELECT    *

    FROM      data_to_pivot

    PIVOT     ( MIN (t_f)

              FOR  APP  IN ( 'App1'  AS valid_for_app1

                             , 'App2'  AS valid_for_app2

                             , 'App3'  AS valid_for_app3

                             , 'App4'  AS valid_for_app4

                             , 'App5'  AS valid_for_app5

                             )

              )

    ORDER BY  object_id

    ;

   

    I want dynamic where App1.. n number change.

Thanks a lot

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 14 2017
Added on Aug 17 2017
0 comments
222 views