Repeat Rows N times According to column values mentioned.

Question:

id	TextValue
3	Binod
2	Vikash

Expected Output 1:

TextValue

Binod

Binod

Binod

Vikash

Vikash

SELECT t.TextValue

FROM #temp t

CROSS JOIN (SELECT ROW_NUMBER() over(order by OBJECT_ID) as rn from sys.columns) as t1

WHERE t.id >= t1.rn

ORDER BY t.TextValue

Expected Output 2:

TextValue	level
Binod	1
Binod	2
Binod	3
Vikash	1
Vikash	2

WITH  CTE AS

(

SELECT TextValue, 1 as level

FROM #temp

UNION ALL

SELECT t.TextValue,level+1 

FROM #temp t join CTE c on t.TextValue = c.TextValue

WHERE level < t.id

)

SELECT * FROM CTE ORDER BY TextValue