Dynamic Pivot Help

User_BU3NG

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