Need some quick help on SQL.....DTS packages.
I’ve loaded data from text files into a table which is in the following format.....
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.....
I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......
Any suggestions will be highly appreciated.
One way is to use the CLR Aggregate function for string concatenation shown in the BOL
and then run a query like this
select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
group by ((Seq_No-1) / 4)+1
select * from dest
please refer to this post
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))