Re: Multiple columns -> comma-seperated list in one column

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 Oct 1999 11:14:22 -0400
Message-ID: <YmcYODoCf8lHy49mUGHnQQiI4eIW@4ax.com>

A copy of this was sent to Adam Hasselbalch Hansen <di991284_at_diku.dk> (if that email address didn't require changing) On Thu, 28 Oct 1999 17:06:04 +0200, you wrote:

>Hi....
>
>I need a little help.
>
>Lets say I have a table with the following values:
>
>COL1 COL2
>--------- ---------
>VAL11 VAL21
>VAL11 VAL22
>VAL11 VAL23
>VAL11 ...
>VAL12 VAL21
>VAL12 VAL24
>VAL12 ...
>... ...
>
>Now, what I want to do, is to see it as follows:
>
>COL1 COL2
>--------------- ------------------------
>VAL11 VAL21, VAL22, VAL23, ...
>VAL12 VAL21, VAL24, ...
>... ...
>
>Anybody have any idea how to do that, using a simple SQL or PL/SQL
>statement?
>
>Kind Regards
>Adam

ou do not mention a database version. this works in Oracle8i, release 8.1 and up (it explicity will NOT work in 8.0 and before -- see further down for 8.0 and before)....

tkyte_at_8i> create or replace function transpose( p_key_name in varchar2,

  2                                        p_key_val  in varchar2,
  3                                        p_other_col_name in varchar2,
  4                                        p_tname     in varchar2 )

  5 return varchar2
  6 as

  7      type rc is ref cursor;
  8      l_str    varchar2(4000);
  9      l_sep    varchar2(1);
 10      l_val    varchar2(4000);
 11  
 12      l_cur    rc;

 13 begin
 14

 15      open l_cur for 'select '||p_other_col_name||'
 16                        from '|| p_tname || '
 17                       where ' || p_key_name || ' = :x ' using p_key_val;
 18  
 19      loop
 20          fetch l_cur into l_val;
 21          exit when l_cur%notfound;
 22          l_str := l_str || l_sep || l_val;
 23          l_sep := ',';
 24      end loop;
 25      close l_cur;
 26  
 27      return l_str;

 28 end;
 29 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> column t format a30
tkyte_at_8i> 
tkyte_at_8i> select deptno, count(*), min(sal), max(sal),
  2         transpose( 'deptno', deptno, 'ename', 'emp' ) t from emp

  3 group by deptno;

    DEPTNO COUNT(*) MIN(SAL) MAX(SAL) T

---------- ---------- ---------- ---------- ------------------------------
        10          3       1300       5000 CLARK,KING,MILLER
        20          5        800       3000 SMITH,JONES,SCOTT,ADAMS,FORD
        30          6        950       2850 ALLEN,WARD,MARTIN,BLAKE,TURNER
                                            ,JAMES

tkyte_at_8i>
tkyte_at_8i> select deptno, count(*), min(sal), max(sal),   2 transpose( 'deptno', deptno, 'job', 'emp' ) t from emp   3 group by deptno;

    DEPTNO COUNT(*) MIN(SAL) MAX(SAL) T

---------- ---------- ---------- ---------- ------------------------------
        10          3       1300       5000 MANAGER,PRESIDENT,CLERK
        20          5        800       3000 CLERK,MANAGER,ANALYST,CLERK,AN
                                            ALYST

        30          6        950       2850 SALESMAN,SALESMAN,SALESMAN,MAN
                                            AGER,SALESMAN,CLERK

tkyte_at_8i>
tkyte_at_8i> select deptno, count(*), min(sal), max(sal),   2 transpose( 'deptno', deptno, 'hiredate', 'emp' ) t from emp   3 group by deptno;

    DEPTNO COUNT(*) MIN(SAL) MAX(SAL) T

---------- ---------- ---------- ---------- ------------------------------
        10          3       1300       5000 09-JUN-81,17-NOV-81,23-JAN-82
        20          5        800       3000 17-DEC-80,02-APR-81,09-DEC-82,
                                            12-JAN-83,03-DEC-81

        30          6        950       2850 20-FEB-81,22-FEB-81,28-SEP-81,
                                            01-MAY-81,08-SEP-81,03-DEC-81

tkyte_at_8i>
tkyte_at_8i> select deptno, count(*), min(sal), max(sal),   2 transpose( 'deptno', deptno, 'sal', 'emp' ) t from emp   3 group by deptno;

    DEPTNO COUNT(*) MIN(SAL) MAX(SAL) T

---------- ---------- ---------- ---------- ------------------------------
        10          3       1300       5000 2450,5000,1300
        20          5        800       3000 800,2975,3000,1100,3000
        30          6        950       2850 1600,1250,1250,2850,1500,950

In 8.0 and before, we could not do the dynamic sql trick. We can create a transpose function for each column/column/table combo. for example:

scott_at_8.0> create or replace function transpose_ename( p_deptno in number ) return varchar2
  2 as

  3          l_string        varchar2(4000);
  4          l_sep           varchar2(1);
  5  begin
  6          for x in ( select ename from emp where deptno = p_deptno ) loop
  7                  l_string := l_string || l_sep || x.ename;
  8                  l_sep := ',';
  9          end loop;
 10          return l_string;

 11 end;
 12 /

Function created.

scott_at_8.0>
scott_at_8.0> select deptno, count(*), transpose_ename(deptno) t   2 from emp
  3 group by deptno
  4 /

    DEPTNO COUNT(*) T

---------- ---------- ----------------------------------------
        10          3 CLARK,KING,MILLER
        20          5 SMITH,JONES,SCOTT,ADAMS,FORD
        30          6 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 28 1999 - 10:14:22 CDT