Re: Best way to convert rows to column?

"Evan Cao" <eecao_at_yahoo.com> wrote in message news:a618084.0208270438.9f6a4fd_at_posting.google.com...
> Assume two tables EMP(EMP_ID,etc) and Childern(EMP_ID, Child_ID, etc)
> with a Master/Detail relation, meaning
> one employee can have 0 upto five n childern( say limited to 5). Now
> want to have a sql generate the output like the following, Any
> suggestions will be very appreciated.
>
> EMP TABLE DATA Child table Data
> 101 101 1001
> 102 101 1002
> ... 102 1004
> 102 1005
> 102 1006
> ...
>
> Result expected:
>
> EMP_ID Child_1 Child_2 Child_3 Child_4 Child_5
> 101 1001 1002 NULL NULL NULL
> 102 1004 1005 1006 NULL NULL
Create a stored function that returns a varchar with children of a given EMP_ID.
Then use it like this:

      select emp_id , get_emp_children( emp_id )
      from emp;

Here's an example I use to convert a list of column names to a string with comma separated names.
I'm sure it's not the best way, but it works.

create or replace function GET_TDM_CONSTRAINT_FIELDS( sConstraintName in varchar2 ) return varchar2 as

    cursor FieldList( sConstraintName varchar ) is

        select column_name as field
        from user_cons_columns
        where constraint_name = sConstraintName
        order by position;

    sFields varchar2(1000);

  begin

    sFields := '';

    for col in FieldList( sConstraintName )     loop

        if ( length(sFields) + length(col.field) + 2 ) > 1000 then
            RAISE_APPLICATION_ERROR(-20000, 'Error in TDM_CONSTRAINT_FIELDS:
too many items.');
        end if;

        if sFields is NULL then
            sFields := col.field;
        else
            sFields := sFields || ', ' || col.field;
        end if;

    end loop;

    return sFields;

  end;
  / Received on Wed Aug 28 2002 - 15:48:48 CEST