SQL Server Performance Forum – Threads Archive

CONCATENATE(???) aggregate function in MS SQL

Hi I have one table(Table1) that looks like this:
———————————————
ID(int), Comment(varchar(250))
1, abc
2, efg
3, wer
1, sqs
2, bcd
——————————————— I want to create a table(Table2) where I have ID as PrimaryKey and Comments concatenated:
——————————————–
ID(int)PK, Comments(varchar(8000))
1, abcsqs
2, efgbcd
3, wer
——————————————— Is there a CONCATENATE(???) aggregate function in MS SQL? I use the following procedure to create my second Table.
First I created a function:
—————————————————————–
CREATE FUNCTION [dbo].[fn_LongComment] (@ID int)
RETURNS varchar(8000)
AS
BEGIN DECLARE @LongComment varchar(8000)
DECLARE MyCursor CURSOR FAST_FORWARD FOR SELECT Comment FROM Table1 WHERE ID = @ID ORDER BY ID
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @Comment
WHILE @@FETCH_STATUS = 0
BEGIN SET @LongComment = @LongComment + @Comment FETCH NEXT FROM MyCursor INTO @Comment
END
CLOSE MyCursor
DEALLOCATE MyCursor RETURN (@LongComment) END
———————————————————————
Then on Table2 I insert all unique IDs.
Next I run the statement below:
———————————————————————
UPDATE Table2 SET Comment = dbo.fn_LongComment(ID)
——————————————————————— Table1 is about 5 mil records and the UPDATE statement takes forever. Is there a better way of doing this?
Also, what’s next after [varchar(8000)] in terms of size? is [text] the right choice. is it OK to go with [text]?
I think that some ID’s will have very long comments. Thank you,
bolo

---

check this logic:
declare @str varchar(50)
set @str=” select @[email protected]+comments from table1
where ids=1 select @str

---

Thanks ranjitjain, Ho do I write my UPDATE statement? A loop should be generated and each time @str = ”, right?

---

Your idea of creating second table to store concatenated values is not advisible. All you have to do is to display them when needed. Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Madhivanan Failing to plan is Planning to fail

---

Hi Madhivanan, Thanks for the link. Very helpful.
bolo

---

]]>