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.
what data type is the Data column?|||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
|||
hi pramy,
please refer to this post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1
regards
|||
hi Derek,
thanks for ur reply. the data type is of varchar.
|||pramy, you still need help on this issue? if not please mark an answer.
thanks,
derek
|||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))
