Concatenating a field while grouping records

All,

Given multiple records with identical values in all fields except a
single varchar field, is there an efficient query that will group the
records into a single record and concatenate the aforementioned
varchar field into a single field with each of the source records'
values separated by commas?

Example:
Record 1 'Doug' , '1'
Record 2 'Doug' , '2'

Output record 'Doug' , '1,2'

Thanks in advance,
DougSELECT col1,
MIN(CASE seq WHEN 1 THEN col2 END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 5 THEN col2 END),'')
FROM
(SELECT S1.col1, S2.col2, COUNT(*) AS seq
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.col1 = S2.col1
AND S1.col2 <= S2.col2
GROUP BY S1.col1, S2.col2) AS X
GROUP BY col1

--
David Portas
----
Please reply only to the newsgroup
--|||This is trivial with the RAC utility for S2k.
No cursors,no complicated code and no hassles.

More info @.
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite released.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!