Joining together string fields

Hi,
Without using a cursor I need to be able to write a query that will return a text value that contains all the entries in a column appended together for each id. The number of entries per id is not limited.

e.g table X with field tID and tText.
containing
1,'Text 1'
1,'Text 2'
1,'Text 3'
2,'Text 1'
2,'Text 2'
3,'Text 1'
3,'Text 2'
3,'Text 3'
3,'Text 4'
4,'Text 1'

I want the result to be

1,'Text 1,Text 2,Text 3'
2,'Text 1,Text 2'
3,'Text 1,Text 2,Text 3,'Text 4'
4,'Text 4'


Any help would be appreciated.

Regards
Jiminy

Have a look at <a href='http://tinyurl.com/l62h' target='_blank' title='http://tinyurl.com/l62h'<a target="_blank" href=http://tinyurl.com/l62h>http://tinyurl.com/l62h</a></a> as there is some good detail as to why the COALESCE 'trick' that is often recomended in response to such a request has some shortcomings (plus you'll find an answer to your question [<img src='/community/emoticons/emotion-2.gif' alt='' />]). Having said that, I have used it in production code in limited cases with no apparent side effects but test,test,test. If you search google groups for "COALESCE csv" you'll get plenty of hits<br /><br /><br />HTH<br /><br />Jasper Smith

Here is some sample code:


declare @tbl_Test TABLE (ID INT, Text VARCHAR(10))
declare @tbl_New TABLE (ID INT, Text VARCHAR(1000))
declare @cItemList varchar(8000)

INSERT INTO @tbl_Test VALUES(1,'Text 1')
INSERT INTO @tbl_Test VALUES(1,'Text 2')
INSERT INTO @tbl_Test VALUES(1,'Text 3')
INSERT INTO @tbl_Test VALUES(2,'Text 1')
INSERT INTO @tbl_Test VALUES(2,'Text 2')
INSERT INTO @tbl_Test VALUES(3,'Text 1')
INSERT INTO @tbl_Test VALUES(3,'Text 2')
INSERT INTO @tbl_Test VALUES(3,'Text 3')
INSERT INTO @tbl_Test VALUES(3,'Text 4')
INSERT INTO @tbl_Test VALUES(4,'Text 1')

DECLARE @Min INT, @Max INT
select @Min = min(ID) from @tbl_Test
select @Max = max(ID) from @tbl_Test

while @Min <= @Max
begin
IF EXISTS(SELECT ID FROM @tbl_Test WHERE ID = @Min)
BEGIN
SELECT @cItemList = COALESCE(@cItemList + ',' , '') + CAST(Text AS varchar(10))
FROM @tbl_Test
WHERE ID = @Min

INSERT INTO @tbl_New
VALUES (@Min,@cItemList)
END

SET @Min = @Min + 1
SET @cItemList = NULL

END

SELECT * from @tbl_New

Sanette

SQL Developer
JustEnough Software Corporation

quote:Walking on water and coding of a spec is easy, as long as both are frozen - A friend