String concatenation
-
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
BEGINSELECT @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
BEGINSELECT @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 > @CURRIDEND/* 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 > @CURRC1END/* WHILE @CURRC1 <= @MAXC1 */SET NOCOUNT OFF
OUTPUT FOR THIS CODEC1 RESULT
----------- ----------
1 AABCC1 RESULT
----------- ----------
2 ADE -
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,'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
-
Thanks, Ron. I can use your code to eliminate one of my WHILE loops.
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply