Re: SQL Question - Select clause within Select List

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/01
Message-ID: <8jkvl5$4kd$1@nnrp1.deja.com>

In article <8jduf4$2hi$1_at_nnrp1.deja.com>,   logo_palanisamy_at_hotmail.com wrote:
> I have a SQL question for the table structure given below.
>

[snip]

> I am not interested in the above output. I want the output to be like
> the one given below.
>
> DNAME ENAME(S)
> -------------- -------------------------------------------
> ACCOUNTING CLARK, KING, MILLER
> RESEARCH SMITH, ADAMS, FORD, SCOTT, JONES
> SALES ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD
>
> I thought the following query might help. But it fails.
>
> SQL> select b.dname, (select a.ename from emp a where a.deptno =
> b.deptno)
> 2 from dept b;
> select b.dname, (select a.ename from emp a where a.deptno = b.deptno)
> *
> ERROR at line 1:
> ORA-01427: single-row subquery returns more than one row
>
> How do I concatenate the names and make it to one big ENAMES column?
> Do I have to pretty much write a function that
> returns all the employee names for a given department and use it in
 the
> select statement as given below?
>
> select dname, enames_fn(deptno) from dept;
>
> Are the better alternatives?
>
> Thanks in advance,
> Logo Palanisamy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

 The first example uses static sql and hence you would need to code a routine per column/table you wished to "list" on. The second, more generic example, may be used on any table/column pair as it uses dynamic sql:

First we will set up a demo table. We have 2 columns A and B.  We desire output in the format:

A-value1            b-value1 b-value2 b-value3 ....
A-value2            b-value1 b-value2 ....

ask_tom_at_OSI1.WORLD> column t format a30
ask_tom_at_OSI1.WORLD> drop table t;
Table dropped.

ask_tom_at_OSI1.WORLD> create table t
  2 ( a varchar2(25),
  3 b varchar2(25)
  4 );

Table created.

ask_tom_at_OSI1.WORLD> insert into t values ( '210','5000' );
ask_tom_at_OSI1.WORLD> insert into t values ( '210','5001' );
ask_tom_at_OSI1.WORLD> insert into t values ( '210','5002' );
ask_tom_at_OSI1.WORLD> insert into t values ( '220','6001' );
ask_tom_at_OSI1.WORLD> insert into t values ( '220','6002' );
ask_tom_at_OSI1.WORLD> commit;

Commit complete.

Our first implementation simply uses static sql to select all of the values for B from T for a given A and string them together:

ask_tom_at_OSI1.WORLD> create or replace
  2 function get_transposed( p_a in varchar2 )   3 return varchar2
  4 is

  5      l_str  varchar2(2000) default null;
  6      l_sep  varchar2(1) default null;
  7  begin
  8      for x in ( select b from t where a = p_a ) loop
  9          l_str := l_str || l_sep || x.b;
 10          l_sep := '-';
 11      end loop;
 12      return l_str;

 13 end;
 14 /

Function created.

ask_tom_at_OSI1.WORLD>
ask_tom_at_OSI1.WORLD> select a, get_transposed( a ) t   2 from t
  3 group by a
  4 /

A                         T
------------------------- ------------------------------
210                       5000-5001-5002
220                       6001-6002

Our next example is more complex. We will pass in the name of the 'key' column (the column to pivot on), a value for that column, the name of the column to actually select out and string together and finally the table to select from:

ask_tom_at_OSI1.WORLD> create or replace

  2  function transpose( p_key_name in varchar2,
  3                      p_key_val  in varchar2,
  4                      p_other_col_name in varchar2,
  5                      p_tname     in varchar2 )

  6 return varchar2
  7 as

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

 14 begin
 15

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

 30 end;
 31 /

Function created.

ask_tom_at_OSI1.WORLD>
ask_tom_at_OSI1.WORLD> REM List the values of "B" for a given value
ask_tom_at_OSI1.WORLD> REM of "A" in the table "T"
ask_tom_at_OSI1.WORLD>
ask_tom_at_OSI1.WORLD> select a, transpose( 'a', a, 'b', 't' ) t

  2 from t
  3 group by a
  4 /

A                         T
------------------------- ------------------------------
210                       5000-5001-5002
220                       6001-6002

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jul 01 2000 - 00:00:00 CDT