how to combine text data rows into single row?

Question

Hi!!!!

 Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

 

Seq_No	Row_No	Data
1	1	ABC
2	2	DEF
3	3	GHI
4	4	JKL
5	1	MNO
6	2	PQR
7	3	STU
8	4	VWX

 

 

 

 

 

  

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

 

Record_No	Data
1	ABCDEFGHIJKL
2	MNOPQRSTUVWX

 

 

 

 I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

Answer 1

what data type is the Data column?

Answer 2

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

 

Answer 3

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

 

Answer 4

hi Derek,

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

Answer 5

pramy, you still need help on this issue? if not please mark an answer.

 

thanks,

derek

Answer 6

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))