Friday, July 28, 2006 9:59 AM
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.
Friday, July 28, 2006 8:17 PMModeratorwhat data type is the Data column?
Saturday, July 29, 2006 1:55 AM
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
Saturday, July 29, 2006 10:35 PM
please refer to this post
Monday, July 31, 2006 5:44 AM
thanks for ur reply. the data type is of varchar.
Wednesday, August 02, 2006 7:15 PMModerator
pramy, you still need help on this issue? if not please mark an answer.
Wednesday, August 09, 2006 6:50 AM
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))