Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007

    Unanswered: Formatted Display

    Hi Experts,

    Could you help me in framing a query for the following requirement.

    I have a table called test with the following data

    id Name
    1 A
    1 B
    1 C
    2 A
    2 B

    My output should look like this

    1 A,B,C
    2 A,B

    I am able to use the analytical functions and get the output as follows
    1 A
    2 A

    Any valuable ideas to display the output with commas is welcomed.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Search for "PIVOT"; 11g has this function, but if you don't use that Oracle database version, you'll have to implement it yourself. Tom Kyte wrote the "STRAGG" function; also, you may search for "CONCAT_ALL" written by James Padfield as a derivation of Tom Kyte's "STRAGG".

    Now that you know what to search for, I believe you'll be able to complete your task.

  3. #3
    Join Date
    Nov 2007


    here is some sample code that can be modified

    Pivot queries
    We create a table that assigns skills to users. A user can have 1 or more skills.
    create table t_ (
      usr  Varchar2(10),
      skl  Varchar2(10)
    Filling the table: 
    insert into t_ values ('Jwa','skinning');
    insert into t_ values ('Jwa','piercing');
    insert into t_ values ('Jwa','tanning' );
    insert into t_ values ('Jwa','writing');
    insert into t_ values ('Jwa','reading');
    insert into t_ values ('Rag','skinning');
    insert into t_ values ('Rag','tanning' );
    insert into t_ values ('Rag','molding');
    insert into t_ values ('Qqq','swimming');
    Now, we're interested in finding out which user has which skills. We desire to have on column per user. Following the steps outlined at the beginning of this page, we create a pipelined function: 
    create or replace type t_vc as table of varchar2(4000);
    create or replace function pivot return t_vc pipelined
      v_last_usr t_.usr%type := null;
      v_line  varchar2(4000);
      for r in (select usr, skl from t_ order by usr) loop
        if v_last_usr is null then
          v_line:=rpad(r.usr,11) || ': ';
          v_last_usr := r.usr;
        end if;
        if r.usr <> v_last_usr then
          pipe row(v_line);
          v_line:=rpad(r.usr,11) || ': ';
          v_last_usr := r.usr;
        end if;
        v_line := v_line || rpad(r.skl,11);
      end loop;
      pipe row(v_line);
    end pivot;
    And displaying the result: 
    select * from table(pivot);
    And the result is: 
    Jwa        : skinning   piercing   writing    tanning    reading
    Qqq        : swimming
    Rag        : skinning   molding    tanning
    Cleaning up: 
    drop table t_;
    drop function pivot;
    drop type t_vc;

Posting Permissions

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