|
|
Posted by Erland Sommarskog on 11/15/05 00:42
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|