Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Displaying Data in certain format - pl sql

    Hi,

    I need to report data from my commision table in specific format.

    I have attached expected format in an excel sheet named as 'Expected_Output'.

    I have tried a program but i could not get the exact output.Please refer to the attachment 'My_Code.txt'.

    My base Table is Employee which is as follows :-
    EmpID Month Year Amout Pos
    11 Jan 2010 1100 1
    11 May 2010 100 5
    11 Sept 2010 50 9
    14 Feb 2010 200 2
    14 Jun 2010 2000 6
    14 Dec 2010 1000 12
    23 Jan 2010 200 1
    23 Apr 2010 100 4
    44 Apr 2010 1000 4
    44 Jan 2011 1000 13

    Pos - i have derived the relative position of the Months based on 01 Jan 2010.
    I have used this field in my program..


    TABLE EMP - it has only Employee IDS

    EMP ID
    11
    14
    23
    44

    There is a flaw in my SQL design. Please Advice based on my exisitng approach or any new way of doing the same.

    Thanks & Regards,
    Subhotech
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    What i believe you want is a pivot table, this is an example using SQL*Plus:
    Code:
    set linesize 1000
    variable  emp_ref_cur    refcursor
    declare
      v_sql       varchar2( 32767 );
     begin
       v_sql  :=  'select *
                    from  ( select empid, year || '-' || month as month, sum( amount ) as amount
                             from  employee
                            group by empid, year || '-' || month )
                    pivot ( sum( amount )
                     for month in(';
    
       for r in( select distinct year || '-' || month as month
                  from  employee )
       loop
         v_sql  :=  v_sql || '''' ||  r.month || ''',';
       end loop;
    
       v_sql  :=  rtrim( v_sql, ',' ) || ' ) )';
       open :emp_ref_cur for v_sql;
     end;
     /
    
    print emp_ref_cur

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •