i have table data in following:
tableid vendor id details 1 sys *hamburger rolls <*hamburger rolls> 2 sys *wheat <wheat> 3 sys *2.99 <2.99> 4 sys *1364 <1364> 5 sys *white <white> 6 sys *1.99 <1.99> 7 sys *1363<1363> 8 sys *hotdog rolls <*hotdog rolls> 9 sys *wheat <wheat> 10 sys *white <white> 11 sys *1.49 <1.49>i need find way query data better formatted results list or possibly xml file. notice data in details column goes in order below item name associated item. id result set along lines of:
vendorid item itemtype price itemid sys hambergurer rolls wheat 2.99 1364 sys hambergurer rolls white 1.99 1363 sys hotdog rolls wheat sys hotdog rolls white 1.49or
<sys> <hamburger rolls> <wheat> <price>2.99</price> <itemid>1364</itemid> </wheat> <white> <price>1.99</price> <itemid>1363</itemid> <white> </hamburger rolls>but i'm having hard time generating query either way format data way need it. please help!
i place in answer keep both approaches clean:
declare @tbl table(tableid int,vendor_id varchar(10),details varchar(100)); insert @tbl values (1,'sys','*hamburger rolls <*hamburger rolls>') ,(2,'sys','*wheat <wheat>') ,(3,'sys','*2.99 <2.99>') ,(4,'sys','*1364 <1364>') ,(5,'sys','*white <white>') ,(6,'sys','*1.99 <1.99>') ,(7,'sys','*1363<1363>') ,(8,'sys','*hotdog rolls <*hotdog rolls>') ,(9,'sys','*wheat <wheat>') ,(10,'sys','*white <white>') ,(11,'sys','*1.49 <1.49>'); cleaneddata ( select row_number() over(order tbl.tableid) rowinx ,tbl.tableid ,tbl.vendor_id ,rtrim(substring(tbl.details,2,charindex('<',tbl.details)-2)) detailsclean ,case when substring(innerinfo.data,1,1)='*' 'product' when isnumeric(innerinfo.data)=1 , charindex('.',innerinfo.data)>0 'price' when isnumeric(innerinfo.data)=1 'itemid' else 'madeof' end linetype @tbl tbl cross apply ( select replace(replace(substring(tbl.details,charindex('<',tbl.details),1000),'<',''),'>','') ) innerinfo(data) ) ,distinctvendors ( select distinct vendor_id cleaneddata ) select dv.vendor_id ,product.detailsclean item ,madeof.detailsclean itemtype ,price.detailsclean price ,itemid.detailsclean itemid distinctvendors dv cross apply ( select cd1.* cleaneddata cd1 cd1.vendor_id=dv.vendor_id , cd1.linetype='product' ) product outer apply ( select cd2.* cleaneddata cd2 cd2.vendor_id=dv.vendor_id , cd2.rowinx between product.rowinx+1 , isnull( (select top 1 x.rowinx cleaneddata x x.linetype='product' , x.rowinx>product.rowinx order x.rowinx desc)-1,100000) , cd2.linetype='madeof' ) madeof outer apply ( select cd3.* cleaneddata cd3 cd3.vendor_id=dv.vendor_id , cd3.rowinx between madeof.rowinx+1 , madeof.rowinx+3 , cd3.linetype='price' ) price outer apply ( select cd3.* cleaneddata cd3 cd3.vendor_id=dv.vendor_id , cd3.rowinx between madeof.rowinx+1 , madeof.rowinx+3 , cd3.linetype='itemid' ) itemid the result
vendor_id item itemtype price itemid sys hamburger rolls wheat 2.99 1364 sys hamburger rolls white 1.99 1363 sys hotdog rolls wheat 1.49 null sys hotdog rolls white 1.49 null 
Comments
Post a Comment