Home > Default > Rows into columns

Rows into columns

November 30Hits:2
Advertisement
hi
i have data in this structure
EMP_ID RANK_ID ENROL_DAT
398 419      24-MAR-02
398 254      14-MAR-67
398 542      02-DEC-84
392 419      24-MAR-02
392 542      10-MAY-65
392 531      23-MAR-83
2545 419     01-JUN-86
2545 411     24-MAR-02
2354 419     04-MAY-85
2354 412     24-MAR-02
2270 421     08-JAN-85
2270 380      24-MAR-02
2211 712      29-AUG-84
2211 650      24-MAR-02
2016 800      30-OCT-83
2016 693      26-DEC-05
574 402     09-MAY-07
574 410      26-DEC-05
574 422      31-MAR-03
574 430      24-FEB-99
574 432      08-FEB-72
574 436      17-MAY-80
I want output like this & I have multiple rank_id for different employees ( like promotion channel) then I want to sort on date
Emp_id 1st.Date 2ndDate 3rd Date 4th Date 5th Date 6th Date
574 09-MAY-07 26-DEC-05 31-MAR-03 24-FEB-99 08-FEB-72 17-MAY-80
2016 30-OCT-83 26-DEC-05

Answers

If you use decode function then you have to named all the condition(in your case rank_id i guess) manually. If you want to achieve it - you can use it in Dyanamic SQL inside any Pl/SQL block.
There is one APi avialable i guess. You can check that in forum whether that satisfy your requirement or not.
The given query posted by one of our member here in this forum. Here, it is showing how you can use API to achieve your requirement -
The following works on 10R2 -
create or replace type NColPipe as object
l_parm varchar2(10),   -- The parameter given to the table function
rows_requested number, -- The parameter given to the table function
ret_type anytype,      -- The return type of the table function
rows_returned number,  -- The number of rows currently returned by the table function
static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
return number,
static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
return number,
static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
return number,
member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
return number,
member function ODCITableClose( self in NColPipe )
return number,
static function show( p_parm in varchar2, p_rows_req in number := 2 )
return anydataset pipelined using NColPipe
create or replace type body NColPipe as
static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
return number
is
   atyp anytype;
begin
   anytype.begincreate( dbms_types.typecode_object, atyp );
   if p_parm = 'one'
   then
     atyp.addattr( 'one'
                 , dbms_types.typecode_varchar2
                 , null
                 , null
                 , 10
                 , null
                 , null
   elsif p_parm = 'two'
   then
     atyp.addattr( 'one'
                 , dbms_types.typecode_varchar2
                 , null
                 , null
                 , 10
                 , null
                 , null
     atyp.addattr( 'two'
                 , dbms_types.typecode_varchar2
                 , null
                 , null
                 , 10
                 , null
                 , null
   else
     atyp.addattr( p_parm || '1'
                 , dbms_types.typecode_varchar2
                 , null
                 , null
                 , 10
                 , null
                 , null
     atyp.addattr( p_parm || '2'
                 , dbms_types.typecode_varchar2
                 , null
                 , null
                 , 10
                 , null
                 , null
     atyp.addattr( p_parm || '3'
                 , dbms_types.typecode_number
                 , 10
                 , 0
                 , null
                 , null
                 , null
   end if;
   atyp.endcreate;
   anytype.begincreate( dbms_types.typecode_table, rtype );
   rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
   rtype.endcreate();
   return odciconst.success;
exception
   when others then
     return odciconst.error;
end;  
static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
return number
is
   elem_typ sys.anytype;
   prec pls_integer;
   scale pls_integer;
   len pls_integer;
   csid pls_integer;
   csfrm pls_integer;
   tc pls_integer;
   aname varchar2(30);
begin
   tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
   sctx := NColPipe( p_parm, p_rows_req, elem_typ, 0 );
   return odciconst.success;
end;
static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
return number
is
begin
   return odciconst.success;
end;
member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
return number
is
begin
   anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
   for i in self.rows_returned + 1 .. self.rows_requested
   loop
     outset.addinstance;
     outset.piecewise();
     if self.l_parm = 'one'
     then
       outset.setvarchar2( to_char( i ) );
     elsif self.l_parm = 'two'
     then
       outset.setvarchar2( to_char( i ) );
       outset.setvarchar2( 'row: ' || to_char( i ) );
     else
       outset.setvarchar2( 'row: ' || to_char( i ) );
       outset.setvarchar2( 'row: ' || to_char( i ) );
       outset.setnumber( i );
     end if;
     self.rows_returned := self.rows_returned + 1;
   end loop;
   outset.endcreate;
   return odciconst.success;
end;
member function ODCITableClose( self in NColPipe )
return number
is
begin
   return odciconst.success;
end;
end;
And to use it
SQL> select * from table( NColPipe.show( 'test', 3 ) );
test1      test2           test3
row: 1     row: 1              1
row: 2     row: 2              2
row: 3     row: 3              3
SQL>  select * from table( NColPipe.show( 'two', 5 ) );
one        two
1          row: 1
2          row: 2
3          row: 3
4          row: 4
5          row: 5
SQL> select * from table( NColPipe.show( 'one' ) );
one
1
2
SQL> Regards.
Satyaki De.

Read other 8 answers

Tags:

Related Articles

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 1.031 s.