This discussion is archived
4 Replies Latest reply: Apr 18, 2013 11:30 PM by Subramanian Meyyappan RSS

Group by cluase

Subramanian Meyyappan Expert
Currently Being Moderated
hi , i had requirement something like printing employee id in first row and in next row should print the employee details. like this for employee say as eg:
employee id   || employee name || employee addr.

100

  -                    frank                    xyx

101 

-                     mike                     xxxx
- using oracle db 10g is this possible by using group by...
  • 1. Re: Group by cluase
    Pradhyumn (PS) Explorer
    Currently Being Moderated
    Hi,
    Try like following code:
    select decode(ename,null,to_char(empno),'-') empnum,ename,sal from 
    (
    select empno,ename, sal from emp
    union all
    select empno,null ename, null sal from emp
    )
    order by empno,ename desc
    PS
  • 2. Re: Group by cluase
    jeneesh Guru
    Currently Being Moderated
    Another way without UNION
    select decode(g,0,empno) emp_no,
           decode(g,1,ename) ename,
           decode(g,1,sal) sal
    from
    (
      select empno,ename,sal,
             grouping_id(rownum) g
      from emp
      group by rollup(rownum),empno,ename,sal
    )
    order by empno;
    
    EMP_NO ENAME      SAL
    ------ ---------- ---
      7369                
           SMITH      800 
      7499                
           ALLEN      1600 
      7521                
           WARD       1250
    ....
  • 3. Re: Group by cluase
    Manik Expert
    Currently Being Moderated
    IF this requirement is to just print the values from table, you can simply use:
    SELECT    a.empid
           || CHR (10)
           || '-'
           || LPAD (a.empname || '     ' || a.sal, 30, ' ')
              paddedoutput
      FROM t a;
    Output:
    PADDEDOUTPUT
    --------------------------------------------------------------
    
    123
    -                Manik1     100
    
    1234
    -                Manik2     100
    
    12345
    -                Manik3     100
    
    123456
    -                Manik4     100
    Cheers,
    Manik.
  • 4. Re: Group by cluase
    Subramanian Meyyappan Expert
    Currently Being Moderated
    hi

    Pradhyumn (PS), jeneesh,Manik thanks for helping in immedie.

    first of-fall, i would say sorry for late response. On that day i finish it. anyhow i cant explore the code due to policy and rights. jeneesh, meets my requirement slightly.

    by the way i have a column for the query. value of the column is will be unique i made a groupby against it. i get the result. but i go with union.

    Manik,PS you may misunderstood. sorry for that it's my mistake. i should phrase my question in a correct format. here after i won't do that too.
    anyhow thanks for helping :)

Legend

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