Split a string without delimiter in TSQl

Question

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

I want to have this

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    

thank you

Answer 1

You can join onto an auxiliary numbers table. I am using spt_values for demo purposes but you should create a permanent one.

WITH Nums
     AS (SELECT number
         FROM   master..spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1000),
     Result(Size, TCount, TDevice)
     AS (SELECT 2,  5,'E01'
         UNION ALL
         SELECT 4.5,3,'W02E01')
SELECT Size,
       COUNT(*) * TCount             AS TCount,
       SUBSTRING(TDevice, number, 1) AS TDevice
FROM   Result
       JOIN Nums
         ON Nums.number <= LEN(TDevice)
GROUP  BY Size,
          TCount,
          SUBSTRING(TDevice, number, 1) 

Answer 2

;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 Fiddle

Advantage: It doesn't require any User defined function (UDF) to be created.