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