-
1. Re: SQL Query Help
Boobal Ganesan Aug 16, 2017 11:18 AM (in response to 2931302)Can you please explain what your desired output's logic is?
-
2. Re: SQL Query Help
AndrewSayer Aug 16, 2017 11:21 AM (in response to 2931302)2931302 wrote:
Hi,
I have following 3 tables :
Table1 1 APP1 2 APP2 4 APP3 8 APP4 Table2 MEMBER_ID Used_in 1 1 2 3 3 5 4 15 5 12 Table3 Object_ID Member Name 1 MEMBER_1 2 MEMBER_2 3 MEMBER_3 4 MEMBER_4 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 Also, This table3 I am using for another columns output with hierarchy combinations.
Thanks
How do you get from the inputs to the outputs?
How are the tables related? It would be super helpful if you shared create table and insert statements so we can demo for ourselves.
Why does the object_id=4 row have valid for app4 = true when the only mention of app4 is in table1 with an unnamed column valued 8, that 8 doesn't match up to anything as far as I can see?
-
3. Re: SQL Query Help
2931302 Aug 16, 2017 11:46 AM (in response to AndrewSayer)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
-
4. Re: SQL Query Help
AndrewSayer Aug 16, 2017 11:57 AM (in response to 2931302)2931302 wrote:
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
Oh yuck.
Why not just have your apps 1,2,3,4 and then instead of a row in table 2 that says
5 12
Have rows
5,3
5,4
That way, your queries are EASY. Your indexing is EASY. You don't have to waste time figuring out how to do everything
-edit
Fixed my table 2 to reference the new obvious PKs for table 1
-
5. Re: SQL Query Help
2931302 Aug 16, 2017 12:07 PM (in response to AndrewSayer)These tables, i am not designing. also these tables comes with Some product. Hence i need to get the data from the defined tables.
-
6. Re: SQL Query Help
Frank Kulash Aug 16, 2017 12:13 PM (in response to 2931302)Hi,
That sounds like a job for BITAND; something like:
WITH data_to_pivot AS
(
SELECT t3.object_id, t3.member_name
, t1.col_3
, CASE
WHEN t1.col_2 = BITAND (t1.col_2, t2.used_in)
THEN 'TRUE'
ELSE 'FALSE'
END AS t_f
FROM table_1 t1
CROSS JOIN table_2 t2
JOIN table_3 t3 ON t3.object_id = t2.member_id
)
SELECT *
FROM data_to_pivot
PIVOT ( MIN (t_f)
FOR col_3 IN ( 'APP1' AS valid_for_app1
, 'APP2' AS valid_for_app2
, 'APP3' AS valid_for_app3
, 'APP4' AS valid_for_app4
)
)
ORDER BY object_id
;
If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test it.
-
7. Re: SQL Query Help
2931302 Aug 16, 2017 12:13 PM (in response to Frank Kulash)Sure will create a complete inserts : and what you said is right. I do want to understand, what is bitmask.
Bitmask representation inidcating which cube the member is used (may be ORed to indicate multiple usage): 1=XX; 2=YY; 4=ZZ
Thanks
-
8. Re: SQL Query Help
AndrewSayer Aug 16, 2017 1:02 PM (in response to 2931302)2931302 wrote:
These tables, i am not designing. also these tables comes with Some product. Hence i need to get the data from the defined tables.
Why not ask the vendor of this product to provide you with usable SQL for your requirements?
-
9. Re: SQL Query Help
2931302 Aug 16, 2017 1:46 PM (in response to 2931302)I am looking out for more Generic solution, as again I see here App1, App2 are hard coded, some times App's are more some times less.
Also tables i created just for this purpose and also with some data. No constraints are there in the tables as these are just for testing.
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
Thanks a lot
-
10. Re: SQL Query Help
2931302 Aug 16, 2017 3:24 PM (in response to 2931302)Dear Frank, I tried this query but result i am not getting I see some where i made mistake.
WITH data_to_pivot AS ( SELECT t3.object_id, t3.membername , t1.ID, APP , CASE WHEN t1.ID = BITAND (t1.ID, t2.usedin) THEN '0' ELSE '1' 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 MEMBERNAME IN ( 'APP1' AS valid_for_app1 , 'APP2' AS valid_for_app2 , 'APP3' AS valid_for_app3 , 'APP4' AS valid_for_app4 ) ) ORDER BY object_id ; -
11. Re: SQL Query Help
Frank Kulash Aug 16, 2017 3:47 PM (in response to 2931302)2931302 wrote:
I am looking out for more Generic solution, as again I see here App1, App2 are hard coded, some times App's are more some times less.
Also tables i created just for this purpose and also with some data. No constraints are there in the tables as these are just for testing.
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
Thanks a lot
You posted 2 CREATE TABLE statements for ct_table3, but none for ct_table1.
All of the the INSERT statements are trying to put stings (such as '1') into NUMBER columns. Never do that. Only insert NUMBER values into NUMBER columns. NUMBER literals do not have single quotes, so you should do things like this:
INSERT INTO CT_TABLE3" (OBJECT_ID, MEMBERNAME) VALUES ( 5 , 'MEMBER_5');
You're right; the query in reply #6 requires you to hard-code the values. You could make this more generic, for example, look for the 10 lowest values of id in ct_table1. (If there happen to be fewer than 10 rows in the table, nothing bad will happen.) However, the only way to get exactly as many columns as you need, and/or to have them named with values found in the table (like VALID_FOR_APP1) is to use Dynamic SQL.
For more on this topic, see:
Re: rows data will go side by side
and
-
12. Re: SQL Query Help
Frank Kulash Aug 16, 2017 4:29 PM (in response to 2931302)Hi,
2931302 wrote:
Dear Frank, I tried this query but result i am not getting I see some where i made mistake.
WITH data_to_pivot AS ( SELECT t3.object_id, t3.membername , t1.ID, APP ... Don't include t1.id here. PIVOT will try to GROUP BY all the columns not mentioned in the PIVOT clause, but you don't want to GROUP BY id; you want all id's for the same member grouped together on the same row.
...
FOR MEMBERNAME IN ( 'APP1' AS valid_for_app1 ... The corresponding line in reply #6 was:
FOR col_3 IN ( 'APP1' AS valid_for_app1
where coll_3 was the string column in table1, that contained values like 'APP1'.
If the string column in ct_table1 is called APP, then you want to say "FOR APP", not "FOR MEMBERNAME", like this:
FOR app IN ( 'App1' AS valid_for_app1
Also, in your original message, the string values were all upper-case. In reply #9, you posted sample data where they included lower-case letters. Make sure the string literals (like 'App1') match the data exactly, case-sensitive.
-
13. Re: SQL Query Help
2931302 Aug 17, 2017 11:42 AM (in response to 2931302)Dear Frank,
Thank you, It works now, but the now question is, the columns are hard coded, i want to be dynamic. As I see several examples, but all are with direct table but mine doing pivot from result of query. I am confused now how to make dynamic from the result of the query. Can you please tell me?
Thanks
-
14. Re: SQL Query Help
Frank Kulash Aug 17, 2017 1:13 PM (in response to 2931302)Hi,
2931302 wrote:
...
but the now question is, the columns are hard coded, i want to be dynamic.
...
Did you read the pages linked at the bottom of reply #11? If you have trouble applying any of those techniques to you own tables, then post your best attempt, and explain exactly what the problem is.
You'll get better replies faster if you start a new thread for this. This thread started as a question about BITAND, with no mention of dynamic SQL. In your own words, "but now the question is" something else, so it really belongs in a new thread, with a subject line such as "Dynamic Pivot", to attract people who are good at and interested in the same kind of issue you have. You can copy and past the sample data you already posted in reply #9, if that's appropriate.
As I see several examples, but all are with direct table but mine doing pivot from result of query. I am confused now how to make dynamic from the result of the query.
If you want to treat the result set of a query as if it were a table, then put the query in a WITH clause; then you can reference the result set anywhere you want to just like you can reference a table. For example:
WITH original_query_results AS
(
SELECT ...
)
-- From this point on, you can use original_query_results the same way as you use a table or view
SELECT ...
FROM original_query_results
...