Prev: Oracle Data-Files Binary Format
Next: Any book on Oracle DBMS's internals?
From: The Magnet on 19 May 2010 17:55
I've modified this select statement so that it will take a column from
multiple rows and concatenate them and delimit them. The value
returned is this:

2:Media","1:Pages","5:Trading","3:Links

However, using the query below, I cannot find a way to get the
beginning and ending quotes. Help?


v_select := '
WITH data AS (
SELECT user_id, username, first_name, last_name, email,
u.service_id || ''' || v_colon || ''' || service_name services,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) rn,
COUNT(*) OVER () cnt
FROM commentary.services s, commentary.user_services u, customer
c, customer_account ca
WHERE c.customer_id = ca.customer_id
AND c.customer_id = u.user_id
AND LOWER(c.email) = ''' || p_email || '''
AND ca.password = ''' || p_password || '''
AND u.service_id = s.service_id)
SELECT user_id, username, first_name, last_name, email,
LTRIM(SYS_CONNECT_BY_PATH(services,''","''),''","'') services
FROM data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR user_id = user_id AND PRIOR rn = rn-1
ORDER BY user_id';
From: Jens Kammler on 20 May 2010 13:07

"The Magnet" <art(a)unsu.com> schrieb :
> I've modified this select statement so that it will take a column from
> multiple rows and concatenate them and delimit them. The value
> returned is this:
>
> 2:Media","1:Pages","5:Trading","3:Links
>
> However, using the query below, I cannot find a way to get the
> beginning and ending quotes. Help?
>

Untestet, what about?


'"' || LTRIM(SYS_CONNECT_BY_PATH(services,''","''),''","'') || '"'
services



 | 
Pages: 1
Prev: Oracle Data-Files Binary Format
Next: Any book on Oracle DBMS's internals?