String concatenation

Question

Post reply

String concatenation

Clyde Coon

SSC Rookie

Points: 27
More actions
December 29, 2005 at 9:22 am

#103009

In this system an invoice can have multiple comments attached to it. I need to concatenate one or more comments into a single string.
The following procedural-like code works by stepping thru the row ids. Isn't there a more SQL-like way to get the desired result?
CREATE TABLE #TEMP1 (
C1 int NOT NULL,
C2 char(1) NOT NULL,
ROWID int IDENTITY (1,1) NOT NULL)
INSERT #TEMP1 VALUES (1,'A')
INSERT #TEMP1 VALUES (1,'A')
INSERT #TEMP1 VALUES (1,'B')
INSERT #TEMP1 VALUES (1,'C')
INSERT #TEMP1 VALUES (2,'A')
INSERT #TEMP1 VALUES (2,'D')
INSERT #TEMP1 VALUES (2,'E')
/* DESIRED OUTPUT, CONCATENATE COLUMN C2 FOR EACH C1
C1RESULT
1AABC
2ADE
*/
DECLARE @MAXC1 int, @CURRC1 int,@MAXID int, @CURRID int, @RESULT char(10)
SET NOCOUNT ON
/* Find the maximum and mininum values for column C1 */
SELECT @MAXC1 = MAX(C1),
@CURRC1 = MIN(C1)
FROM #TEMP1
/* Process each value in column C1 */
WHILE @CURRC1 <= @MAXC1
BEGIN
SELECT @RESULT = ''
/* Find the maximum and minimum ROWID for the current C1 value */
SELECT @MAXID = MAX(ROWID),
@CURRID = MIN(ROWID)
FROM #TEMP1
WHERE C1 = @CURRC1
/* Concatenate each C2 for the current C1 value */
WHILE @CURRID <= @MAXID
BEGIN
SELECT @RESULT = RTRIM(@RESULT) + C2
FROM #TEMP1
WHERE ROWID = @CURRID
/* Find the next ROWID to process */
SELECT @CURRID = MIN(ROWID)
FROM #TEMP1
WHERE C1 = @CURRC1
AND ROWID > @CURRID
END/* WHILE @CURRID <= @MAXID *//* Display the result */
SELECT C1 = @CURRC1,RESULT = @RESULT
/* Find the next C1 value to process */
SELECT @CURRC1 = MIN(C1)
FROM #TEMP1
WHERE C1 > @CURRC1
END/* WHILE @CURRC1 <= @MAXC1 */SET NOCOUNT OFF
OUTPUT FOR THIS CODE
C1 RESULT
----------- ----------
1 AABC
C1 RESULT
----------- ----------
2 ADE

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ron k SSCertifiable Points: 6103 More actions · Answer 1

SET NOCOUNT ON

CREATE TABLE #TEMP1 (

C1 int NOT NULL,

C2 char(1) NOT NULL,

ROWID int IDENTITY (1,1) NOT NULL)

INSERT #TEMP1 VALUES (1,'A')

INSERT #TEMP1 VALUES (1,'B')

INSERT #TEMP1 VALUES (1,'C')

INSERT #TEMP1 VALUES (2,'A')

INSERT #TEMP1 VALUES (2,'D')

INSERT #TEMP1 VALUES (2,'E')

declare @comments varchar(500)

(edited to remove space between comments)

select @comments = isnull(@comments, '') + c2

from #TEMP1

Where c1 = 2

select @comments

drop table #temp1

--------

ADE

Hope that helps,

ron

Clyde Coon SSC Rookie Points: 27 More actions · Answer 2

Thanks, Ron. I can use your code to eliminate one of my WHILE loops.

noeld SSC Guru Points: 96590 More actions · Answer 3

Clyde,

I think you can get rid of ALL your loops by simply enclosing Ron's logic in a UDF

create function dbo.concatComments( @id int)

returns varchar(500)

as

begin

declare @comments varchar(500)

select @comments = isnull(@comments, '') + Comment

from YourTable

Where id = @id

return @comments

end

-- and then simply

select id, dbo.concatComments( id )

from YourTable

group by id

Cheers,

* Noel