nigelrivett (12/11/2012)

---

This will do something like that.

It caters for any number of fields - just amend the final select for the number required

declare @s-2 varchar(8000)

select @s-2 = '30;38469;1|31;38470;1|32;38471;1|33;38472;1|34;38473;1|35;38474;1|36;38475;1|37;38476;1|38;38477;1|'

select @s-2 = '30;38469|31;38470|32;38471|33;38472|34;38473|35;38474|36;38475|37;38476|38;38477|'

declare @rowterm varchar(1) = '|'

declare @fieldterm varchar(1) = ';'

;with cte as

(

select rowstrt = 1, rowend = charindex(@rowterm,@s)-1, seq = 1

union all

select rowstrt = rowend+2, rowend = charindex(@rowterm,@s,rowend+2)-1, seq = seq + 1

from cte where charindex(@rowterm,@s,rowend+2)<>0

) ,

cte2 as

(

select s = substring(@s, rowstrt,rowend-rowstrt+1), seq

from cte

) ,

cte3 as

(

select fldstrt = 1, fldend = charindex(@fieldterm,s)-1, seq, s, fldseq = 1 from cte2

union all

select fldstrt = fldend+2,

fldend = case when charindex(@fieldterm,s,fldend+2) <> 0 then charindex(@fieldterm,s,fldend+2)-1 else len(s) end,

seq, s, fldseq = fldseq+1

from cte3 where fldend < len(s)

)

select s1.s, s2.s, s3.s

from

(select seq, s = substring(s,fldstrt,fldend-fldstrt+1) from cte3 where fldseq = 1) s1

join (select seq, s = substring(s,fldstrt,fldend-fldstrt+1) from cte3 where fldseq = 2) s2 on s1.seq = s2.seq

left join (select seq, s = substring(s,fldstrt,fldend-fldstrt+1) from cte3 where fldseq = 3) s3 on s1.seq = s3.seq