This discussion is archived
7 Replies Latest reply: Jul 11, 2013 6: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 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points