Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Reading column into a string variable
Michel Cadot wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1163452050.065616.276080_at_f16g2000cwb.googlegroups.com...
> | 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
> |
> | 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
Very nice improvement.
I admit that I was concerned that placing a WHERE clause in that spot
would prevent the CONNECT BY PRIOR syntax from working properly - but
it works exactly as you describe. While not apparent with this small
data set, the WHERE clause can make a significant impact on larger data
sets. Modifcation of my query (same output as your query above):
SELECT
SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2) NAME_LIST
FROM
(SELECT
NAME,
ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION,
COUNT(*) OVER () ROW_COUNT
FROM
T1)
WHERE
ROW_POSITION=ROW_COUNT
CONNECT BY PRIOR
ROW_POSITION=ROW_POSITION-1
START WITH
ROW_POSITION=1;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Tue Nov 14 2006 - 04:35:51 CST