concatenating x rows into 1 row

[ozpeppers]

Hello guys

I have a table (table1) with four columns:

CustomerID,
CallID,
NotesID,
Notes

There are many customers, who have many CallID. Each CallID has many NoteID, ie 1,2,3, each notesID has a one to one with Notes which holds Varchar(255) strings.

I.E.
CustID CallID NoteID Notes
----------------------------------------------
21300380 1 1 a
21300380 2 1 b
21398328 1 1 c
21398328 2 1 d
100035149 1 1 e
100046025 1 1 f
100046025 1 2 g
100046025 1 3 h
100046025 2 1 i
100046025 3 1 j

I have to create a new table (table2) with the fields:

CustomerID
CallID
Notes

The new table (table2) field Notes should be a concatenation of the Notes field of table1.

I.E. using above data

CustID CallID Notes
----------------------------------------------
21300380 1 a
21300380 2 b
21398328 1 c
21398328 2 d
100035149 1 e
100046025 1 fgh
100046025 2 i
100046025 3 j

Hope thats clear enough for anyone to help...

Cheers in advance

Mark

 

[LarryWFox]

Until Microsoft adds an aggregate string concat function; or, you write your own extended function, the only solution I am aware of is to use a cursor.


DECLARE
@CustID int,
@CallID int,
@NotesAll varchar(3000)

DECLARE ncur CURSOR FOR
SELECT DISTINCT
T1.CustID, T1.CallID
FROM Table1 T1
ORDER BY 1, 2

OPEN ncur

FETCH NEXT FROM ncur
INTO @CustID, @CallID

/*
Loop is executed once for each group of Table1 records with the same CustID and CallID value.
*/

WHILE @@FETCH_STATUS = 0
BEGIN
SET @NotesAll = '' -- null string

-- For all records in this group, concatenates all "Notes" fields, in order by "NoteID"

SELECT @NotesAll = @NotesAll + RTRIM(Notes) + ' '
FROM Table1
WHERE CustID = @CustID
AND CallID = @CallID
ORDER BY NoteID

INSERT Table2
VALUES( @CustID, @CallID, @NotesAll )

FETCH NEXT FROM ncur
INTO @CustID, @CallID
END

CLOSE ncur
DEALLOCATE ncur