Aggregating String Data ...!

[Guest_imported]

I have a table where there is a int field (id)and a str data.I want to concatenate all the string data for which the int data (id) is the same.
Eg: id str1
1 aaa
1 bbb
1 ccc
2 yyy
2 zzz
I want the result as
1 aaabbbccc
2 yyyzzz

Can anyone give a soluting for this.

 

[jhall156]

I'm sure there must be an easier and cleaner way, but this works:
DECLARE @strnew varchar(255),
@idnew int,
@strold varchar(255),
@idold int,
@finstr varchar(255)

DECLARE @Results table(id int, str varchar(255))

DECLARE myCursor CURSOR FOR SELECT id, str FROM <yourtable> ORDER BY id, str
OPEN myCursor
FETCH NEXT FROM myCursor INTO @idold, @strold
SELECT @finstr = @strold
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM myCursor INTO @idnew, @strnew
IF @idnew = @idold AND @@FETCH_STATUS = 0
SELECT @finstr = @finstr + @strnew
ELSE IF @@FETCH_STATUS = 0
BEGIN
INSERT @Results SELECT @idold, @finstr
SELECT @idold = @idnew,
@finstr = @strnew
END
END
INSERT @Results SELECT @idold, @finstr
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM @Results

 

[tlbroadbent]

Here is a another procedure that works.

Declare @ID int, @str Varchar(400)
--Using table variabl in SQL 2000
Declare @Tbl Table (ID int, AllStr1 Varchar(2000))

--Use temp table in SQL 7 or earlier
--Create Table tbl (ID int, AllStr1 Varchar(2000))

Declare ID_Cursor Cursor For
Select Distinct ID From TableName

Open ID_Cursor

Fetch Next From ID_Cursor Into @id
While @@Fetch_Status=0
Begin
Set @str=''
Select @str = @str + insnull(str1,'')
From TableName Where ID=@id
Insert @Tbl Values (@id, @str)
--Change @tbl to tbl if using temp table
Fetch Next From ID_Cursor Into @id
End

Close ID_Cursor
Deallocate ID_Cursor

--Change @tbl to tbl if using temp table
Select * From @Tbl Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.