SQL Server Concatenate Certain Subsquent Rows onto Parent Row Using Set-Based Solution -
i trying parse baiv2 banking file in t-sql after importing table. file composed of rows , each row has 2 leading numbers. if leading number row happens "88", it's "continuation row" , meant extension of previous row (to prevent rows getting wide in file). example file below:
01,123456,123456,123456,1419,1,80,,2/
02,123456,123456,1,123456,,usd,2/
03,123456,usd,010,0,,,015,0,,,020,0,,,025,0,,,030,0,,,040,0,,,045,0,,/
88,050,0,,,055,0,,,057,0,,,060,0,,,072,0,,,074,0,,,100,123456,1,,270,123456,1,/
88,400,123456,35,,470,123456,35,/
16,275,123456,s,123456,0,0,,/
88,zba xfer bank account 123456
16,475,123456,z,123456,123456/
88,check-ird
16,475,123456,z,123456,123456/
88,check-ird
how used set-based sql query append rows leading "88" previous row? looks xml based query might work. able accomplish complicated loops , variables it's inefficient i'd set-based solution. appreciated.
i need "88" records appended previous rows:
01,123456,123456,123456,1419,1,80,,2/
02,123456,123456,1,123456,,usd,2/
03,123456,usd,010,0,,,015,0,,,020,0,,,025,0,,,030,0,,,040,0,,,045,0,,,050,0,,,055,0,,,057,0,,,...
16,275,123456,s,123456,0,0,,,zba xfer bank account 123456
16,475,123456,z,123456,123456,check-ird
16,475,123456,z,123456,123456,check-ird
this work if have consecutive int
id column in table
edit rewritten recursive cte. there easier way, runs:
;with x (id,txt,lvl)as (select id,cast(txt varchar(max)),1 lvl @table id = 1 union select a.id ,cast((case when left(a.txt,2) = '88' cast(b.txt varchar(max)) else '' end)+ a.txt varchar(max)) ,case when left(a.txt,2) = '88' b.lvl else b.lvl + 1 end @table inner join x b on a.id = b.id + 1) ,y ( select *,rank() over(partition lvl order len(txt) desc) rnk x ) select id,replace(replace(txt,'/',''),'88,','') txt y rnk = 1
Comments
Post a Comment