4 Replies Latest reply: Apr 19, 2013 1:30 AM by Subramanian Meyyappan RSS

    Group by cluase

    Subramanian Meyyappan
      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)
          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
            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
              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
                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 :)