how to combine text data rows into single row?

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

CliffordDibble-MSFT at 2007-8-30 >

hi Derek,

thanks for ur reply. the data type is of varchar.

Pramy at 2007-8-30 >

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

Pramy at 2007-8-30 >