O2T Column Switch to Row

Database
Enthusiast

O2T Column Switch to Row

Hi :

There is one Oracle user define function, the function is Column switch to Row, example-- 


Select col1  from test


Col1


--------------------------


1


2


3


4


Select strcat(col1) from test


Strcat(col1)


--------------------------


1 2 3 4

     The strcat is that function , we can archive it by table join , but the performance maybe not good ,is there someone had finished it by

UDF in Teradata?

     The function strcat's source code:

---------------------------------------------------------------

create or replace type strcat_type as object

      (

        currentstr varchar2(4000),

        currentseprator varchar2(8),

        static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,

        member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,

        member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,

        member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number

      );

create or replace type body strcat_type is

      static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is

      begin

        sctx := strcat_type('',' ');

        return ODCIConst.Success;

      end;

      member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is

      begin

       if self.currentstr is null then

self.currentstr := value;

 else

          self.currentstr := self.currentstr ||currentseprator || value;

        end if;

        return ODCIConst.Success;

       end;

      member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is

      begin

        returnValue := self.currentstr;

        return ODCIConst.Success;

      end;

      member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is

      begin

        if ctx2.currentstr is null then

          self.currentstr := self.currentstr;

        elsif self.currentstr is null then

          self.currentstr := ctx2.currentstr;

        else

          self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;

        end if;

        return ODCIConst.Success;

      end;

      end;

CREATE OR REPLACE FUNCTION strcat (input VARCHAR2)

RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;     

   ----------------------------------------------------------------

     Have a good day!