4 Replies Latest reply on Aug 14, 2015 4:00 PM by Gary Graham-Oracle

    How to  write for below output

    913349

      Hi All,

      i am using oracle 11g.

       

      I have  a table  emp_det

      The table is having below columns and data.

       

      Role_Name ,consumer_id ,location

      IT            1,2,3                   QTP

      HR          3,4,5                   BDK

      TECH     7,8,9                   SJP

      IT            9,7,9                  NJP

       

       

      expected result:

       

      Role_Name_1   CONSUMER_ID_1  LOCATION_1  ROLE_NAME_2  CONSUMER_ID_2   LOCATION_2   ROLE_NAME_3  CONSUMER_ID_3   LOCATION_3

             IT                       1,2,3                      QTP              HR                           3,4,5                  BDK              TECH                                7,8,9          SJP

             IT                       9,7,9                       NJP           null                              null                    null                   null                                 null             null

       

      please help me.

      Regards

      Damby

        • 1. Re: How to  write for below output
          aJohny

          Damby,

           

          You might need to close this thread and raise this is in SQL & PL/SQL

          • 2. Re: How to  write for below output
            Gaz in Oz

              1  WITH list(role_name, consumer_id, location) AS

              2  (SELECT 'IT',   '1,2,3', 'QTP' FROM  dual UNION ALL

              3   SELECT 'HR',   '3,4,5', 'BDK' FROM  dual UNION ALL

              4   SELECT 'TECH', '7,8,9', 'SJP' FROM  dual UNION ALL

              5   SELECT 'IT',   '9,7,9', 'NJP' FROM  dual)

              6  SELECT it.*,

              7         MAX(DECODE(it.location, 'QTP', it2.role_name, NULL))   role2,

              8         MAX(DECODE(it.location, 'QTP', it2.consumer_id, NULL)) consumer2,

              9         MAX(DECODE(it.location, 'QTP', it2.location, NULL))    location2,

            10         MAX(DECODE(it.location, 'QTP', it3.role_name, NULL))   role3,

            11         MAX(DECODE(it.location, 'QTP', it3.consumer_id, NULL)) consumer3,

            12         MAX(DECODE(it.location, 'QTP', it3.location, NULL))    location3

            13  FROM  (SELECT role_name, consumer_id, location

            14         FROM   list

            15         WHERE  role_name = 'IT') it,

            16        (SELECT role_name, consumer_id, location

            17         FROM   list

            18         WHERE  location = 'BDK'

            19         AND    role_name != 'IT') it2,

            20        (SELECT role_name, consumer_id, location

            21         FROM   list

            22         WHERE  location = 'SJP'

            23         AND    role_name != 'IT') it3

            24* GROUP BY it.role_name, it.consumer_id, it.location;

             

            role      consumer  LOCATION  ROLE2     CONSUMER2 LOCATION2 ROLE3     CONSUMER3 LOCATION3

            --------- --------- --------- --------- --------- --------- --------- --------- ---------

            IT        9,7,9     NJP

            IT        1,2,3     QTP       HR        3,4,5     BDK       TECH      7,8,9     SJP

             

            2 rows selected.

            • 3. Re: How to  write for below output
              913349

              Hi Gaz,

              Thanks for your reply. But the problem is you are making all values here statisc

              WITH list(role_name, consumer_id, location) AS

                2  (SELECT 'IT',   '1,2,3', 'QTP' FROM  dual UNION ALL

                3   SELECT 'HR',   '3,4,5', 'BDK' FROM  dual UNION ALL

                4   SELECT 'TECH', '7,8,9', 'SJP' FROM  dual UNION ALL

                5   SELECT 'IT',   '9,7,9', 'NJP' FROM  dual)

               

              suppose i have 10 roles and in that case i cant write so many time consumer_id and location values. donot consider there are only 4 records are there in that table . it might be  maximum 15 roles are there.

               

              here my question is based on the role  the column will increase  suppose there are 5 different roles are there then 5*3 15 columns should be there.

              if  one role is repeating then it will come as above out put.

              • 4. Re: How to  write for below output
                Gary Graham-Oracle

                Actually, as implied by aJohny, this is not the correct discussion space for your question. Please post only those questions directly related to the SQL Developer tool here.

                 

                As for the "WITH list" being static, that is just a standard technique to simulate the table data you provided with your question.  If the solution provided by Gaz is not robust, then all the more reason to post to the relevant forum.