Concatenating a field while grouping records

[Doug Shokes]

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,
Doug

[David Portas]

SELECT 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
--

[Groucho]

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!