Generating row/columns for individual chars using CTE

I have the below input table

Input

ID  Row Data
1   1   a2b
1   2   p1d1
2   1   abcd

Expected Output

ID  RowCol  Char开发者_如何学运维s
1   a1  a
1   b1  X
1   c1  X
1   d1  b
1   a2  p
1   b2  X
1   c2  d
1   d2  X
2   a1  a
2   b1  b
2   c1  c
2   d1  d

Each numbers in the data column will be treated as that many X's. So if the expand the first resord which is 'a2b' it becomes aXXB. that is the length will be 4. Representing in columns it will be a, b ,c and d. And since, it is in the first row, therefore, the output will be

ID  RowCol  Chars
1   a1  a
1   b1  X
1   c1  X
1   d1  b

The ddl is as under

Declare @t table(ID int , Row int, Data varchar(10))
Insert into @t 
Select 1, 1,'a2b' Union All Select 1,2,'p1d1' Union All Select 2,1,'abcd'

Looking for a cte based solution.

Thanks in advance

As i promised i would make a better solution today. I know you will like and most likely use it.

DECLARE @t TABLE(ID INT , Row INT, Data VARCHAR(10)) 
Insert INTO @t  
SELECT 1, 1,'a2b' UNION All SELECT 1,2,'p1d1' UNION All SELECT 2,1,'abcd'

;WITH cte(id, row, num, data) 
AS ( 
SELECT id, row, 1 num,CAST(data as VARCHAR(10)) data
FROM @t
UNION ALL 
SELECT ch.id, row, CH.num +1,  CAST(REPLACE(ch.data, ch.num, REPLICATE('X', ch.num)) as VARCHAR(10))
FROM cte ch
WHERE ch.num < 9 )
, cte2(id, rowcol, row, num, data, chars, LEVEL)  as
(SELECT  id, CHAR(97) + CAST(row AS CHAR) rowcol, row, num, data, SUBSTRING(data, 1, 1), 1 LEVEL
FROM cte 
where num =  9
UNION all
SELECT id, CHAR(97 + LEVEL) + CAST(row AS CHAR) rowcol, row, num, data, SUBSTRING(data, LEVEL + 1, 1), LEVEL + 1
FROM cte2 ch
where LEVEL < LEN(data)
)
SELECT ID, rowcol, chars 
FROM CTE2
ORDER BY id, data, rowcol