Re: using select to concatenate into a string?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 22 Jul 2006 03:50:36 -0700
Message-ID: <1153565436.111846.123400@b28g2000cwb.googlegroups.com>

kimberly.shaffer_at_gmail.com wrote:
> Charles Hooper wrote:
> > kimberly.shaffer_at_gmail.com wrote:
> > > dilemma: using a select statement ONLY
> Thanks - I looked on asktom but I couldn't find anything that used
> straight sql without a function or that didn't use aggregate or
> grouping. I just want to concatenate a number of records from one
> field into one string. Two fields won't work.
>
> thx!
>
> kshaffer

I know that the syntax in the SQL statement that I provided looks a bit odd. If you use straight SQL for this task, you must use aggregate functions to collapse multiple result rows into a single row. With the help of the DECODE function and the aliased ROWNUM, only one value will be presented to the MAX function. If you have a table in the database with a column named ID, try the SQL statement to see if it does what you need - just replace ACCOUNT with the name of the table. If you have no tables with a column named ID, replace every ID in the SQL statement with the appropriate column name.

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);

If the input was:
ID ROWNUM (ROWNUM is an automatically generated column that increments by 1)
APE 1
CAT 2
CHICKEN 3
DOG 4
MOUSE 5 The output of the above SQL statement would be: APE, CAT, CHICKEN, DOG, MOUSE Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 22 2006 - 05:50:36 CDT