在TSQl中拆分没有分隔符的字符串

I have a result set from SELECT Statement, how can i split one column without any delimiter this is my result

Size TCount TDevice
 2     5      E01
 4.5   3      W02E01

我想要这个

Size TCount  TDevice
 2      5     E
 2      5     0
 2      5     1
4.5     3     W
4.5     6     0      (we have 2 times of 0)
4.5     3     2
4.5     3     1    

谢谢

推荐答案

;with cte as
(
 select Size,TCount,
         substring(TDevice, 1, 1) as Chars,
         stuff(TDevice, 1, 1, '') as TDevice
  from t1
  union all
  select Size,TCount,
         substring(TDevice, 1, 1) as Chars,
         stuff(TDevice, 1, 1, '') as TDevice
  from cte
  where len(TDevice) > 0
)
select distinct Size,sum(TCount),Chars
from cte
group by Size,Chars

SQL 小提琴

优点:不需要创建任何用户定义函数(UDF).