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

Popular posts from this blog

python - Healpy: From Data to Healpix map -

c - Bitwise operation with (signed) enum value -

xslt - Unnest parent nodes by child node -