Re: using select to concatenate into a string?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Jul 2006 13:31:17 -0700
Message-ID: <1153513877.859875.212410@i42g2000cwa.googlegroups.com>

kimberly.shaffer_at_gmail.com wrote:
> dilemma: using a select statement ONLY
>
> is it possible to get the results to concatenate in a string such as?
>
> select field from table;
>
> original results
> --------------------
> 1 cat
> 2 dog
> 3 mouse
>
> wanted results
> ---------------------
> 1 cat, dog, mouse

Take a look at this SQL statement, which allows up to 20 result rows to be appended together:

SELECT

  MAX(DECODE(RN,1,ID,NULL))||
  MAX(DECODE(RN,2,', '||ID,NULL))||
  MAX(DECODE(RN,3,', '||ID,NULL))||
  MAX(DECODE(RN,4,', '||ID,NULL))||
  MAX(DECODE(RN,5,', '||ID,NULL))||
  MAX(DECODE(RN,6,', '||ID,NULL))||
  MAX(DECODE(RN,7,', '||ID,NULL))||
  MAX(DECODE(RN,8,', '||ID,NULL))||
  MAX(DECODE(RN,9,', '||ID,NULL))||
  MAX(DECODE(RN,10,', '||ID,NULL))||
  MAX(DECODE(RN,11,', '||ID,NULL))||
  MAX(DECODE(RN,12,', '||ID,NULL))||
  MAX(DECODE(RN,13,', '||ID,NULL))||
  MAX(DECODE(RN,14,', '||ID,NULL))||
  MAX(DECODE(RN,15,', '||ID,NULL))||
  MAX(DECODE(RN,16,', '||ID,NULL))||
  MAX(DECODE(RN,17,', '||ID,NULL))||
  MAX(DECODE(RN,18,', '||ID,NULL))||
  MAX(DECODE(RN,19,', '||ID,NULL))||
  MAX(DECODE(RN,20,', '||ID,NULL))

FROM
  (SELECT
    ID,
    ROWNUM RN
  FROM
    ACCOUNT
  WHERE
    ROWNUM<=10
  ORDER BY
    ID);

The inline view in the SQL statement above just retrieves the first 10 rows of the ACCOUNT table, when sorted alphabetically. The only critical component here is the ROWNUM, which is given an alias of RN.

Outside the inline view, the MAX and DECODE statements are used to append the rows together into a single resulting row. The SQL statement as written will handle up to 20 result rows, even though only 10 are retrieved from the inline view.

The above is SQL statement inspired by Tom Kyte's SQL statement for finding un-indexed foreign keys. As another posted suggested, you will likely find some very advanced methods for appending the results into a single row on the asktom.oracle.com site.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jul 21 2006 - 15:31:17 CDT