Re: Reading column into a string variable

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 14 Nov 2006 06:32:11 +0100
Message-ID: <455954da$0$7731$426a34cc@news.free.fr>

"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1163452050.065616.276080_at_f16g2000cwb.googlegroups.com...
| zomer wrote:
| > Hi,
| > I need to read all entries of a column into a varchar variable. There
| > are about 100 entries.... how can i do it in PL/SQL?
| >
| > Table
| > Name
| > alex
| > brad
| > cynthia
| > .
| > .
| > .
| > .
| > zomer
| >
| > Anticipated output
| > alex,brad,cynthia.....,zomer
| >
| > Thanks.
|
| It should be fairly easy in PL/SQL to loop through the results of the
| query and append the value to a variable. You can also do this
| automatically in SQL, and then just use the result in your PL/SQL code.
|
| The setup:
| CREATE TABLE T1 (NAME VARCHAR2(20));
| INSERT INTO T1 VALUES('alex');
| INSERT INTO T1 VALUES('brad');
| INSERT INTO T1 VALUES('cynthia');
| INSERT INTO T1 VALUES('zomer');
|
| The SELECT statement:
| SELECT
| MAX(SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2)) NAME_LIST
| FROM
| (SELECT
| NAME,
| ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
| FROM
| T1)
| CONNECT BY PRIOR
| ROW_POSITION=ROW_POSITION-1
| START WITH
| ROW_POSITION=1;
|
| The output:
| NAME_LIST
| =========
| alex,brad,cynthia,zomer
|
| How do this work? The inline view retreives each name, and a number
| that indicates the sorted order of the name:
| SELECT
| NAME,
| ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
| FROM
| T1
|
| If we then use a CONNECT BY PRIOR, starting with ROW_POSITION 1, we
| receive as output:
| NAME_LIST
| ====================
| alex
| brad
| cynthia
| zomer
|
| By specifying SYS_CONNECT_BY_PATH, and eliminating the prefixed , we
| obtain:
| NAME_LIST
| ====================
| alex
| alex,brad
| alex,brad,cynthia
| alex,brad,cynthia,zomer
|
| If we then find the maximum value, we are left with the last item:
| NAME_LIST
| =========
| alex,brad,cynthia,zomer
|
| Charles Hooper
| PC Support Specialist
| K&M Machine-Fabricating, Inc.
|

A slighty better query is:

SQL> with
  2 data as (

  3      select name,
  4             row_number() over (order by name) rn,
  5             count(*) over () cnt
  6      from t1

  7 )
  8 select substr(sys_connect_by_path(name,','),2) name_list   9 from data
 10 where rn = cnt
 11 connect by prior rn = rn-1
 12 start with rn = 1
 13 /
NAME_LIST

---

alex,brad,cynthia,zomer

1 row selected.

It avoids the grouping action by precalculating which row will contain the final result.

Regards
Michel Cadot Received on Mon Nov 13 2006 - 23:32:11 CST