Complex Select

From: The Magnet <art_at_unsu.com>
Date: Wed, 19 May 2010 14:55:42 -0700 (PDT)
Message-ID: <40fcdfc0-a402-475b-aa9e-02ab0ef63db6_at_m4g2000vbl.googlegroups.com>

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'; Received on Wed May 19 2010 - 16:55:42 CDT