7 Replies Latest reply: Jul 11, 2013 8:32 AM by Solomon Yakobson RSS

    Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)

    MayankSharma

      There is table called department

      Depno  Employee_id  salary

      101         E12             1000
      101         E13             1000
      101         E14             3000
      101         E15             5000
      102         E16             4000
      102         E17             2000
      102         E18             1000

      102         E19             4000

       

      Now I want result like

      Depno      Employee_id                salary

      101             E12,E13,E14,E15                 10000

      102             E16,E17,E18,E19                 11000

       

      Tell the solution for 10 g SQLplus without creating function (no use of plsql) and also listagg function work in 11g (Need to solve query through sql statements)

        • 1. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
          Tubby

          MayankSharma wrote:

           

          There is table called department

          Depno  Employee_id  salary

          101         E12             1000
          101         E13             1000
          101         E14             3000
          101         E15             5000
          102         E16             4000
          102         E17             2000
          102         E18             1000

          102         E19             4000

           

          Now I want result like

          Depno      Employee_id                salary

          101             E12,E13,E14,E15                 10000

          102             E16,E17,E18,E19                 11000

           

          Tell the solution for 10 g SQLplus without creating function (no use of plsql) and also listagg function work in 11g (Need to solve query through sql statements)

          http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

           

          Cheers,

          • 2. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
            Solomon Yakobson

            SQL> select  *
              2    from  v$version
              3  /

            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
            PL/SQL Release 10.2.0.5.0 - Production
            CORE    10.2.0.5.0      Production
            TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
            NLSRTL Version 10.2.0.5.0 - Production

            SQL> with sample_table as (
              2                        select 101 deptno,'E12' employee_id,1000 salary from dual union all
              3                        select 101,'E13',1000 from dual union all
              4                        select 101,'E14',3000 from dual union all
              5                        select 101,'E15',5000 from dual union all
              6                        select 102,'E16',4000 from dual union all
              7                        select 102,'E17',2000 from dual union all
              8                        select 102,'E18',1000 from dual union all
              9                        select 102,'E19',4000 from dual
            10                       )
            11  select  deptno,
            12          rtrim(xmlagg(xmlelement(e,employee_id,',').extract('//text()') order by employee_id),',') employee_id,
            13          sum(salary) salary
            14    from  sample_table
            15    group by deptno
            16    order by deptno
            17  /

                DEPTNO EMPLOYEE_ID                                  SALARY
            ---------- ---------------------------------------- ----------
                   101 E12,E13,E14,E15                               10000
                   102 E16,E17,E18,E19                               11000

            SQL>

             

            SY.

            • 3. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
              MayankSharma

              I dont know the number of depatmentid emloyeeid and salary that was just a demo table this I can also do

              • 4. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
                Solomon Yakobson


                WITH clause is just a way of creating sample table on-the-fly, since you didn't bother providing CREATE TABLE & INSERT data statements. Solution I posted doesn't care how many employees are in a department and what are their salaries. Just use:

                 

                select  deptno,

                        rtrim(xmlagg(xmlelement(e,employee_id,',').extract('//text()') order by employee_id),',') employee_id,

                        sum(salary) salary

                  from  your_table

                  group by deptno

                  order by deptno

                /

                 

                SY.

                • 5. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
                  chris227

                  Another way

                   

                  with sample_table as (

                  select 101 deptno,'E12' employee_id,1000 salary from dual union all

                  select 101,'E13',1000 from dual union all

                  select 101,'E14',3000 from dual union all

                  select 101,'E15',5000 from dual union all

                  select 102,'E16',4000 from dual union all

                  select 102,'E17',2000 from dual union all

                  select 102,'E18',1000 from dual union all

                  select 102,'E19',4000 from dual

                  )

                   

                  select

                  deptno

                  ,sum_sal as salary

                  ,trim(both ',' from empid_str) as employee_id

                  from (

                  select

                  *

                  from sample_table

                  model

                  partition by (deptno)

                  dimension by (row_number() over (partition by deptno order by employee_id desc) rn)

                  measures ( sum(salary) over(partition by deptno) sum_sal, cast(employee_id as varchar2(4000)) empid_str)

                  rules (

                  empid_str[any] order by rn desc=empid_str[cv()+1]||','||empid_str[cv()]

                  ))

                  where rn = 1

                  order by deptno

                   

                  DEPTNOSALARYEMPLOYEE_ID
                  10110000E12,E13,E14,E15
                  10211000E16,E17,E18,E19
                  • 6. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
                    MayankSharma

                    Thanks Can you explain each key word what it do below

                    rtrim(xmlagg(xmlelement(e,employee_id,',').extract('//text()') order by employee_id),',') employee_id,

                    • 7. Re: Please read question of carefully how to perform in SQLplus10g (dont give ans for 11g or previous release)
                      Solomon Yakobson

                      Each function is documented. Function xmlelement produces xml elements of employee id followed by a comma enclosed tags <e>. And xmlagg aggregates these xmlelements. Extract extarcts text so you get a comma separated list of employee ids. And rtrim removed that trailing comma.

                       

                      SY.