SQL Query Help

Can you please explain what your desired output's logic is?

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?

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

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

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.

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?

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

Re: Report count and sum from many rows into many columns

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.

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

...