SEO

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 (hdenooijer@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, esquel@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

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

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 (hdenooijer@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, esquel@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

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

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