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

<kimberly.shaffer (AT) gmail (DOT) com> a écrit dans le message de news: 1153506780.865682.82470 (AT) h48g200...oglegroups.com...
Yes, search for "pivot" on asktom.oracle.com or google

Regards
Michel Cadot

kimberly.shaffer (AT) gmail (DOT) com wrote:
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.

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

Charles Hooper wrote:

<kimberly.shaffer (AT) gmail (DOT) com> a écrit dans le message de news: 1153546957.343909.12170 (AT) h48g200...oglegroups.com...
Concatenate several rows field is this not grouping rows?
What do you mean with your last sentence "Two fields won't work"?

Regards
Michel Cadot

kimberly.shaffer (AT) gmail (DOT) com wrote:
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.