DECLARE @t TABLE (id int, ScanNumber NVARCHAR(116))
INSERT INTO @t
SELECT 1,--> 000 007 059
'_ _ _ _ _ _ _ _ _
| || || || || | || ||_ |_|
|_||_||_||_||_| ||_| _| _|
' UNION
SELECT 2, --> 490 067 715
' _ _ _ _ _ _ _
|_||_|| || ||_ | | ||_
| _||_||_||_| | | | _|
' UNION
SELECT 3, --> 680 068 279
'_ _ _ _ _ _ _ _ _
|_ |_|| || ||_ |_| _| ||_|
|_||_||_||_||_||_||_ | _|
'
UNION
SELECT 4, --> 490 867 716
' _ _ _ _ _ _ _
|_||_|| ||_||_ | | ||_
| _||_||_||_| | | ||_|
' UNION
SELECT 5, --> X80 867 716
' _ _ _ _ _ _ _
| ||_|| ||_||_ | | ||_
| _||_||_||_| | | ||_|
'
UNION SELECT 6, --> 012 345 678
'_ _ _ _ _ _ _
| | | _| _||_||_ |_ ||_|
|_| ||_ _| | _||_| ||_|
';
with cte_splitgroup as
(
select id,id_h=1,b.number,Char=substring(' '+a.ScanNumber,27*0+0+b.number*3+1,3)
from @t a inner join
master.dbo.spt_values b on b.type='P' and b.number<9
union all
select id,id_h=2,b.number,Char=substring(' '+a.ScanNumber,27*1+1+b.number*3+2,3)
from @t a inner join
master.dbo.spt_values b on b.type='P' and b.number<9
union all
select id,id_h=3,b.number,Char=substring(' '+a.ScanNumber,27*2+2+b.number*3+3,3)
from @t a inner join
master.dbo.spt_values b on b.type='P' and b.number<9
)
,
cte_number as
(select number=0,String='
_
| |
|_|' union all select 1,'
|
|' union all select 2,'
_
_|
|_ ' union all select 3,'
_
_|
_|' union all select 4,'
|_|
|' union all select 5,'
_
|_
_|' union all select 6,'
_
|_
|_|' union all select 7,'
_
|
|' union all select 8,'
_
|_|
|_|' union all select 9,'
_
|_|
_|')
,
cte_r as
(
select a.id,a.number,Value=isnull(rtrim(d.number),'X')
from cte_splitgroup a inner join
cte_splitgroup b on a.id=b.id and a.number=b.number and a.id_h=1 and b.id_h=2 inner join
cte_splitgroup c on a.id=c.id and c.id_h=3 and a.number=c.number left join
cte_number d on char(13)+a.Char+char(13)+b.Char+char(13)+c.Char=d.String
)
,
cte_r1 as
(
select id,number,Value=cast(Value as varchar(9))
from cte_r where number=0
union all
select b.id,b.number,Value=cast(a.Value+b.Value as varchar(9))
from cte_r1 a inner join
cte_r b on a.id=b.id and b.number=a.number+1
)
select id,Value from cte_r1
where number=8
order by id
