Generating row/columns for individual chars using CTE

Question

I have the below input table

Input

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

Expected Output

ID  RowCol  Chars
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

possible duplicate of [Efficient way to string split using CTE](http://stackoverflow.com/questions/6381587/efficient-way-to-string-split-using-cte) — richardtallent, Jun 20 '11 at 07:09
You should try the new version of my code, I spend alot of time on it. It works, howeven i am considering an improvement for tomorrow. — t-clausen.dk, Jun 27 '11 at 19:54

t-clausen.dk · Accepted Answer · 2011-06-28T09:09:46.327

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

Generating row/columns for individual chars using CTE

1 Answers1