1 2 Previous Next 24 Replies Latest reply on Aug 18, 2017 4:44 PM by rp0428

    SQL Query Help

    2931302

      Hi,

       

      I have following 3 tables :

       

          

      Table1
      1APP1
      2APP2
      4APP3
      8APP4
      Table2
      MEMBER_IDUsed_in
      11
      23
      35
      415
      512
      Table3
      Object_IDMember Name
      1MEMBER_1
      2MEMBER_2
      3MEMBER_3
      4MEMBER_4
      5MEMBER_5
      Desired Out put
      Object_IDMember NameVALID FOR APP1VALID FOR APP2VALID FOR APP3VALID For APP4
      1MEMBER_1TRUEFALSEFALSEFALSE
      2MEMBER_2TRUETRUEFALSEFALSE
      3MEMBER_3FALSETRUETRUEFALSE
      4MEMBER_4TRUETRUETRUETRUE

       

       

      Also, This table3 I am using for another columns output with hierarchy combinations.

       

      Thanks

        • 1. Re: SQL Query Help
          Boobal Ganesan

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

          • 2. Re: SQL Query Help
            AndrewSayer

            2931302 wrote:

             

            Hi,

             

            I have following 3 tables :

             

             

            Table1
            1APP1
            2APP2
            4APP3
            8APP4
            Table2
            MEMBER_IDUsed_in
            11
            23
            35
            415
            512
            Table3
            Object_IDMember Name
            1MEMBER_1
            2MEMBER_2
            3MEMBER_3
            4MEMBER_4
            5MEMBER_5
            Desired Out put
            Object_IDMember NameVALID FOR APP1VALID FOR APP2VALID FOR APP3VALID For APP4
            1MEMBER_1TRUEFALSEFALSEFALSE
            2MEMBER_2TRUETRUEFALSEFALSE
            3MEMBER_3FALSETRUETRUEFALSE
            4MEMBER_4TRUETRUETRUETRUE

             

             

            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
              Table1
              PK1APP1
              PK2APP2
              PK4APP3
              PK8APP4
              Table2
              MEMBER_IDUsed_in
              FK(OBJECT_ID)11
              FK(OBJECT_ID)23
              FK(OBJECT_ID)35
              FK(OBJECT_ID)415
              FK(OBJECT_ID)512
              Table3
              Object_IDMember Name
              PK1MEMBER_1
              PK2MEMBER_2
              PK3MEMBER_3
              PK4MEMBER_4
              PK5MEMBER_5
              Desired Out put
              Object_IDMember NameVALID FOR APP1VALID FOR APP2VALID FOR APP3VALID For APP4
              1MEMBER_1TRUEFALSEFALSEFALSE
              2MEMBER_2TRUETRUEFALSEFALSE
              3MEMBER_3FALSETRUETRUEFALSE
              4MEMBER_4TRUETRUETRUETRUE
              5MEMBER_5FALSEFALSETRUETRUE

               

               

              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

                2931302 wrote:

                 

                Table1
                PK1APP1
                PK2APP2
                PK4APP3
                PK8APP4
                Table2
                MEMBER_IDUsed_in
                FK(OBJECT_ID)11
                FK(OBJECT_ID)23
                FK(OBJECT_ID)35
                FK(OBJECT_ID)415
                FK(OBJECT_ID)512
                Table3
                Object_IDMember Name
                PK1MEMBER_1
                PK2MEMBER_2
                PK3MEMBER_3
                PK4MEMBER_4
                PK5MEMBER_5
                Desired Out put
                Object_IDMember NameVALID FOR APP1VALID FOR APP2VALID FOR APP3VALID For APP4
                1MEMBER_1TRUEFALSEFALSEFALSE
                2MEMBER_2TRUETRUEFALSEFALSE
                3MEMBER_3FALSETRUETRUEFALSE
                4MEMBER_4TRUETRUETRUETRUE
                5MEMBER_5FALSEFALSETRUETRUE

                 

                 

                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

                  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

                    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

                      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

                        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

                          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

                            Dear Frank, I tried this query but result i am not getting I see some where i made mistake.

                             

                            WITHdata_to_pivotAS
                            (
                            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

                              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

                              • 12. Re: SQL Query Help
                                Frank Kulash

                                Hi,

                                2931302 wrote:

                                 

                                Dear Frank, I tried this query but result i am not getting I see some where i made mistake.

                                 

                                WITHdata_to_pivotAS
                                (
                                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

                                  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

                                    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

                                    ...

                                    1 2 Previous Next