Concatenating strings from different rows

[Hennie7863]

Hi,

I have table which has the following values :

ID SEQ Text
1 1 A
2 1 B
3 2 C
4 2 D
5 2 E
6 2 F
7 3 G

The result should be :

1 AB
2 CDEF
3 G

Could somebody help me with this? I could use an cursor but the table
could be large and i want a fast solution.

Thanx in advance...

Hennie

[Erland Sommarskog]

Hennie7863 (hd********@hotmail.com) writes:

I have table which has the following values :

ID SEQ Text
1 1 A
2 1 B
3 2 C
4 2 D
5 2 E
6 2 F
7 3 G

The result should be :

1 AB
2 CDEF
3 G

Could somebody help me with this? I could use an cursor but the table
could be large and i want a fast solution.

Unfortunately, if you are on SQL 2000, the cursor is the only
reliable solution. There are tricks with SELECT and UPDATE but
they rely on undefined behaviour, and I would encourge use of them.

On SQL 2005 there is some XML functionality, that solves this problem,
as show in this small demo:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hennie7863]

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

[Hennie7863]

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

[Erland Sommarskog]

Hennie7863 (hd********@hotmail.com) writes:

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:

This too is a solution which depends on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515. This article
is somewhat schizofrenic, since it first says "The correct behavior for an
aggregate concatenation query is undefined." and then goes showing when
it may work after all. Personally, I prefer to not rely on it at all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[prakashdehury]

try with this

you also can use table variable without using #temp table.

create table tab001
(
id int,
SEQ int,
txt char(1)
)

insert into tab001 values (1,1,'A')
insert into tab001 values (2,1,'B')
insert into tab001 values (3,2,'C')
insert into tab001 values (4,2,'D')
insert into tab001 values (5,2,'E')
insert into tab001 values (6,3,'F')
insert into tab001 values (7,3,'G')

create table #tem
(
id int
)
create table #temmData
(
id int,
txtAll varchar(200)
)

insert #tem select Distinct SEQ from tab001
Declare @tSEQ int
DECLARE t_cursor CURSOR FOR
select id from #tem

declare @Notes varchar(4000)

OPEN t_cursor
FETCH NEXT FROM t_cursor INTO @tSEQ

IF @@FETCH_STATUS <> 0
print 'no ID found'

WHILE @@FETCH_STATUS = 0
BEGIN

Select @Notes = ''
Select @Notes = @Notes + ' ' + txt

From tab001 Where SEQ = @tSEQ order by ID DESC print @Notes
insert into #temmData values (@tSEQ, @Notes)
FETCH NEXT FROM t_cursor INTO @tSEQ

END

select * from #temmData

CLOSE t_cursor
DEALLOCATE t_cursor



Hennie7863 wrote: Hi,

I have table which has the following values :

ID SEQ Text
1 1 A
2 1 B
3 2 C
4 2 D
5 2 E
6 2 F
7 3 G

The result should be :

1 AB
2 CDEF
3 G

Could somebody help me with this? I could use an cursor but the table
could be large and i want a fast solution.

Thanx in advance...

Hennie

[Hennie7863]

Well Erland, Thanx for the reply. Hmmm and i thought i found a better
solution than the one shown in my earlier post. Great! I do not like a
cursor in a function, specially when the table is very large. I think
that i'm gone use the function anyway and when i convert the function
to 2005 i will use your other suggestion to implement in this function.
Greetz,

Hennie