3 Replies Latest reply on Mar 31, 2016 11:53 AM by thatJeffSmith-Oracle

    Need to create a pivot table.

    2880712

      Hi,

       

      I need to create a table like below.

       

      Group NameResource NameReadModifyFull Control
      Group1Resource1010
      Group 2Resource2101

      Read : Yes - 1  No -0

      Modify : Yes - 1  No -0

      Full Control : Yes - 1  No -0

       

      My current output table is like below:

      select a.entitled_group_name,e.resource_name ,e.action_name from AVUSER.V_GROUPS_ACCESS_TO_APP a join  AVUSER.t_entitlements e on a.application_id = e.application_id where e.application_id= -2

       

      Group NameResource NameAction
      Group 1Resource 1Read
      group2Resource 2Modify
      group3Resource 3Full Control
      Group 4Resource 4modify

      Is it possible ?

        • 1. Re: Need to create a pivot table.
          jmarton

          It is indeed possible from Oracle 11gR1, if I recall well.

           

          Here is my answer with the example data I created after your post. I have tested it on 12cR1. You can replace group_privs table with your subquery is you need to.

           

          Please mark this thread answered and go to the SQL forums for more detail, where you possibly get quicker, and more elaborate answers.

           

           

           

          create table group_privs (
            group_name varchar2(30)
          , resource_name varchar2(30)
          , action varchar2(30)
          );
          insert into group_privs (group_name, resource_name, action) values ('Group 1', 'Resource 1', 'Read');
          insert into group_privs (group_name, resource_name, action) values ('Group 1', 'Resource 1', 'Modify');
          insert into group_privs (group_name, resource_name, action) values ('Group 2', 'Resource 2', 'Modify');
          insert into group_privs (group_name, resource_name, action) values ('Group 3', 'Resource 3', 'Full Control');
          insert into group_privs (group_name, resource_name, action) values ('Group 4', 'Resource 4', 'Modify');
          commit;
          
          
          select group_name, resource_name
               , nvl(c_read, 0) as col_Read
               , nvl(c_modify, 0) as col_Modify
               , nvl(c_full_control, 0) as col_Full_Control
            from group_privs pivot (max(1) for action -- constant 1 indicates there was a match
                                    in ('Read' as c_Read, 'Modify' as c_Modify, 'Full Control' as c_Full_Control)
                                   )
          ;
          
          
          
          
          
          • 2. Re: Need to create a pivot table.
            Vysakh Suresh - 3035408

            yes. It is possible using the PIVOT'ing concept dear.. but think the count provided by you under the sample output is incorrect..;)

            try this query..

            WITH TEMP1 AS

            (

            SELECT 'GROUP 1' AS GROUP_NAME, 'RESOURCE 1' AS RESOURCE_NAME, 'READ' AS ACTION FROM DUAL

            UNION ALL SELECT 'GROUP 2', 'RESOURCE 2', 'MODIFY' FROM DUAL

            UNION ALL SELECT 'GROUP 3', 'RESOURCE 3', 'FULL CONTROL' FROM DUAL

            UNION ALL SELECT 'GROUP 4', 'RESOURCE 4', 'MODIFY' FROM DUAL

            )

            SELECT * FROM TEMP1

            PIVOT(COUNT(ACTION) FOR ACTION IN ('READ','MODIFY','FULL CONTROL')) ORDER BY GROUP_NAME


            sample output..

            • 3. Re: Need to create a pivot table.
              thatJeffSmith-Oracle

              two things, this topic should be on the SQL space and please don't post screenshots of Toad here...this is the SQL Developer forum