March 10, 2008 at 2:59 pm
Guys,
I have following table where I need to concatenate varchar column.
For example
ID COMMENT
__________________
1 JOHN SMITH
1 SURRENDER
1 TO COPS
I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
March 10, 2008 at 7:26 pm
You have a few choices here: The two best being an UDF and the other being XML.
You can get more information from the following thread. Additionally, Jeff has posted a function that will do the same thing. In some cases the UDF process the data much faster, but you will have to test in your environment to see what suites your needs best.
http://qa.sqlservercentral.com/Forums/Topic465637-149-1.aspx
The XML solution is below:
SELECT id,
STUFF((SELECT ' ' + comment
FROM @t a
WHERE a.ID = b.ID
FOR XML PATH(''))
,1,1,'')AS String
FROM @t b
GROUP BY id
March 10, 2008 at 7:54 pm
There is another way to acheive this result using COALESCE.
CREATE TABLE test
(id int
, comment varchar(255))
INSERT INTO test
VALUES (1, 'JOHN SMITH')
INSERT INTO test
VALUES (1, 'SURRENDER')
INSERT INTO test
VALUES (1, 'TO COPS')
SELECT * FROM test
DECLARE @colList varchar(1000)
SELECT @colList = COALESCE(@colList + ' ', '') + comment
FROM test
WHERE id = 1
SELECT @colList
DROP TABLE test
Note: If there are too many rows to concatenate, change the @collist variable length to varchar(max)
March 11, 2008 at 1:28 pm
Thanks for the reply, when I try to group by multiple columns I get an error using XML path is there any way round it
SELECT id, todate
STUFF((SELECT ' ' + comment
FROM TEST2 a
WHERE a.ID = b.ID and a.todate = b.todate
FOR XML PATH(''))
,1,1,'')AS String
FROM TEST2 b
GROUP BY id, todate
I get the following error message
"Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FOR'.
"
Is there any way round it
Thanks
March 11, 2008 at 1:41 pm
You are missing a comma (,) after todate. Here is the corrected code:
SELECT id, todate,
STUFF((SELECT ' ' + comment
FROM TEST2 a
WHERE a.ID = b.ID and a.todate = b.todate
FOR XML PATH(''))
,1,1,'')AS String
FROM TEST2 b
GROUP BY id, todate
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply