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]

Re: Concatenating strings from different rows

Hennie7863 (hdenooijer@hot mail.com) writes:[color=blue]
> 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.[/color]

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(OrdId List, 1, datalength(OrdI dList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarcha r(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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hennie7863]

Re: Concatenating strings from different rows

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(in t,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(@TextL ist + ' ', '') + 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_ConcatStrin g(seq)
...
From
....

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

Thanx

Hennie

[Hennie7863]

Re: Concatenating strings from different rows

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(in t,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(@TextL ist + ' ', '') + 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_ConcatStrin g(seq)
...
From
....

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

Thanx

Hennie

[Erland Sommarskog]

Re: Concatenating strings from different rows

Hennie7863 (hdenooijer@hot mail.com) writes:[color=blue]
> 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(@TextL ist + ' ', '') + 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:[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[prakashdehury@gmail.com]

Re: Concatenating strings from different rows




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[color=blue]
>From tab001 Where SEQ = @tSEQ order by ID DESC[/color]
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:[color=blue]
> 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[/color]

[Hennie7863]

Re: Concatenating strings from different rows

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