8 Replies Latest reply on Nov 27, 2014 3:35 PM by shiva_0004

    How to group rows and display as single column, with the grouping row value as the column name

    shiva_0004

      Hi,

       

      I have a situation like, I need to group data in rows and display as column, with the grouping row value as the column name.

       

      With the below scenario, you will understand my requirement better,

       

      Below is the EMP table structure with records.

       

      EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
      7369SMITHCLERK790212/17/198080020
      7499ALLENSALESMAN76982/20/1981160030030
      7521WARDSALESMAN76982/22/1981125050030
      7566JONESMANAGER78394/2/1981297520
      7654MARTINSALESMAN76989/28/19811250140030
      7698BLAKEMANAGER78395/1/1981285030
      7782CLARKMANAGER78396/9/1981245010
      7788SCOTTANALYST756612/9/1982300020
      7839KINGPRESIDENT11/17/1981500010
      7844TURNERSALESMAN76989/8/1981150030
      7876ADAMSCLERK77881/12/1983110020
      7900JAMESCLERK769812/3/198195030
      7902FORDANALYST756612/3/1981300020
      7934MILLERCLERK77821/23/1982130010

       

      I want to display the report as below,

       

      It is like, I wanted to display set of Employees for each department, grouped under their respective jobs.

       

      DEPTNOCLERKPRESIDENTMANAGERANALYSTSALESMAN
      10MILLERKINGCLARK
      20SMITH, ADAMSJONESSCOTT, FORD
      30JAMESBLAKEALLEN, WARD, MARTIN, MARTIN, TURNER

       

      I have no clue on where to start on this.

       

      The DB version used in our organization is Oracle 10g.

       

      Please help me in this.

       

      Thank you all in adavnce.

       

      Regards,

      Shiva

        • 1. Re: How to group rows and display as single column, with the grouping row value as the column name
          Etbin

          select *

            from (select deptno,job,listagg(ename,',') within group (order by null) list

                    from emp

                   group by deptno,job

                 )

          pivot (max(list) for job in ('CLERK' as clerk,'PRESIDENT' as president,'MANAGER' as manager,'ANALYST' as analyst,'SALESMAN' as salesman))

           

          DEPTNOCLERKPRESIDENTMANAGERANALYSTSALESMAN
          10MILLERKINGCLARK--
          20ADAMS,SMITH-JONESFORD,SCOTT-
          30JAMES-BLAKE-ALLEN,MARTIN,TURNER,WARD

           

          Regards

           

          Etbin

          • 2. Re: How to group rows and display as single column, with the grouping row value as the column name
            shiva_0004

            Thanks a lot Etbin for helping me on this.

             

            But, in our organization they use Oracle 10g.

             

            My fate, Listagg & Pivot were introduced in Oracle 11g only.

             

            It would be very great if you could guide me to query in Oracle 10g.

             

            Thaks again.

             

            Regards,

            Shiva

            • 3. Re: How to group rows and display as single column, with the grouping row value as the column name
              Frank Kulash

              Hi,

               

              Etbin showed a great way to do this in Oracle 11.2 or higher.

              Since you're using Oracle 9, you can do it this way:

               

              WITH   got_r_num    AS

              (

                  SELECT  deptno, job, ename

                  ,       ROW_NUMBER () OVER ( PARTITION BY  deptno, job

                                               ORDER BY      ename

                                             )   AS r_num

                  FROM    scott.emp

              )

              ,    got_ename_lists    AS

              (

                  SELECT  deptno, job

                  ,       SUBSTR ( SYS_CONNECT_BY_PATH (ename, ',')

                                 , 2

                                 )  AS ename_list

                  FROM    got_r_num

                  START WITH  r_num   = 1

                  CONNECT BY  r_num   = PRIOR r_num + 1

                          AND deptno  = PRIOR deptno

                          AND job     = PRIOR job

              )

              SELECT    deptno

              ,         MAX (CASE WHEN job = 'ANALYST'   THEN ename_list END)   AS analyst

              ,         MAX (CASE WHEN job = 'CLERK'     THEN ename_list END)   AS clerk

              ,         MAX (CASE WHEN job = 'MANAGER'   THEN ename_list END)   AS manager

              ,         MAX (CASE WHEN job = 'PRESIDENT' THEN ename_list END)   AS president

              ,         MAX (CASE WHEN job = 'SALESMAN'  THEN ename_list END)   AS salesman

              FROM      got_ename_lists

              GROUP BY  deptno

              ORDER BY  deptno

              ;

              In any case, you have to know what the specific jobs (e.g., 'ANALYST', 'CLERK', ...) are.  If you want those to be dynamic, then you need to use dynamic SQL to write the main query.

              • 4. Re: How to group rows and display as single column, with the grouping row value as the column name
                Frank Kulash

                Hi, Shiva,

                shiva_0004 wrote:

                ...

                But, in our organization they use Oracle 10g.

                ...

                 

                My apologies!  I thought you said Oracle 9.

                The solution I posted earlier will work in any version, 9.1 (or higher), but if you have 10.1 (or higher) it might be more efficient if you add one more line of code in the middle, like this:

                 

                WITH   got_r_num    AS

                (

                    SELECT  deptno, job, ename

                    ,       ROW_NUMBER () OVER ( PARTITION BY  deptno, job

                                                 ORDER BY      ename

                                               )   AS r_num

                    FROM    scott.emp

                )

                ,    got_ename_lists    AS

                (

                    SELECT  deptno, job

                    ,       SUBSTR ( SYS_CONNECT_BY_PATH (ename, ',')

                                   , 2

                                   )  AS ename_list

                    FROM    got_r_num

                   WHERE   CONNECT_BY_ISLEAF  = 1       -- Works in Oracle 10 (and up)

                    START WITH  r_num   = 1

                    CONNECT BY  r_num   = PRIOR r_num + 1

                            AND deptno  = PRIOR deptno

                            AND job     = PRIOR job

                )

                SELECT    deptno

                ,         MAX (CASE WHEN job = 'ANALYST'   THEN ename_list END)   AS analyst

                ,         MAX (CASE WHEN job = 'CLERK'     THEN ename_list END)   AS clerk

                ,         MAX (CASE WHEN job = 'MANAGER'   THEN ename_list END)   AS manager

                ,         MAX (CASE WHEN job = 'PRESIDENT' THEN ename_list END)   AS president

                ,         MAX (CASE WHEN job = 'SALESMAN'  THEN ename_list END)   AS salesman

                FROM      got_ename_lists

                GROUP BY  deptno

                ORDER BY  deptno

                ;

                If you need to get the different jobs dynamically, see Help for a query to add columns

                • 5. Re: Re: How to group rows and display as single column, with the grouping row value as the column name
                  Etbin

                  Sorry, someone indvertently posted my pivot exercise

                   

                  select deptno,

                         max(decode(job,'CLERK',list)) clerk,

                         max(decode(job,'PRESIDENT',list)) president,

                         max(decode(job,'MANAGER',list)) manager,

                         max(decode(job,'ANALYST',list)) analyst,

                         max(decode(job,'SALESMAN',list)) salesman

                    from (select deptno,job,ltrim(sys_connect_by_path(ename,','),',') list

                            from (select deptno,job,ename,row_number() over (partition by deptno,job order by ename) rn

                                    from emp

                                 )

                           where connect_by_isleaf = 1

                           start with rn = 1

                          connect by prior rn + 1 = rn

                                 and prior deptno = deptno

                                 and prior job = job

                                 and prior sys_guid() is not null

                         )

                  group by deptno

                  order by deptno

                   

                  DEPTNOCLERKPRESIDENTMANAGERANALYSTSALESMAN
                  10MILLERKINGCLARK--
                  20ADAMS,SMITH-JONESFORD,SCOTT-
                  30JAMES-BLAKE-ALLEN,MARTIN,TURNER,WARD

                   

                  Regards

                   

                  Etbin

                  1 person found this helpful
                  • 6. Re: How to group rows and display as single column, with the grouping row value as the column name
                    RajeshReddy

                    Hi Shiva

                    You can have alternate query by using below

                     

                    Select deptno,Case When job='CLERK' then wm_concat(ename) end CLERK,

                                  Case When job='SALESMAN' then wm_concat(ename) end SALESMAN,

                                  Case When job='PRESIDENT' then wm_concat(ename) end PRESIDENT,

                                  Case When job='MANAGER' then wm_concat(ename) end MANAGER,

                                  Case When job='TRAINEE' then wm_concat(ename) end TRAINEE,

                                  Case When job='ANALYST' then wm_concat(ename) end ANALYST

                     

                     

                    from emp

                    group by deptno,job

                    order by 1

                    • 7. Re: How to group rows and display as single column, with the grouping row value as the column name
                      Frank Kulash

                      Hi,

                       

                      Do not use undocumented functions like wm_concat.  Its behavior may change from version to version, or it may disappear.

                       

                      You can create a user-defined function that seems to do the same thing. See

                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

                      I say it "seems to" do the same thing, because we don't really know what wm_concat is doing: it's undocumented.

                      • 8. Re: How to group rows and display as single column, with the grouping row value as the column name
                        shiva_0004

                        Thanks a lot Frank, Etbin & Rajesh.

                         

                        I am working on understanding these queries.

                         

                        Thank you guys again....