Posted by Hennie7863 on 11/15/05 11:10

Hi Erland,

I'm stil working SQL Server 2000. In 2006 i'll starting with SQL Server
2005. From an earlier item i founded the following (not fully
supported) :

Select
c.Seq ,
[Text],
identity(int,1,1) as i
into #T
from Concat C

select Seq,identity(int,1,1) as i
into #Cursor
from #T
group by Seq

declare @i int, @text varchar(8000)

set @i=1

while exists(select * from #cursor where i=@i)
begin

set @text=''
update #T
set @text= [Text] = @text + ' ' + [Text]
where Seq = (select Seq from #cursor where i=@i)

Select * from #T

set @i=@i+1
end

select a.seq,right([text],len([text])-1) as textt
from #T as a
join (select seq, max(i) as i from #T group by seq ) as b on a.i=b.i

But as i read the post you said that this was not a rightful way to do
this so i searched further for a better solution and i found on SQL
Team :

DECLARE @TextList Varchar(100)

SELECT @TextList = COALESCE(@TextList + ' ', '') + CAST(Text AS
varchar(8000)) FROM <Table> WHERE Seq = @iSeq

RETURN (@TextList)

The only problem i've is that i am using this in a function and i want
to build <table> dynamically. I have to use a function because i'm
using this in a query, like this:

SELECT
...
GET_ConcatString(seq)
...
From
....

But you can't build dynamic strings in function because of
blabladiebladiebla.

Thanx

Hennie

[Back to original message]