SQL Puzzle | Convert a string into a table n number of times ( n = length of the string )

Puzzle Statement

One of my friend today asked me a puzzle. The puzzle says you have an input string and you have to insert that string into a table without using a loop. One more condition is there, you have to insert that string number of times the length of that string. E.g. Let’s say that the string is ‘Pawan’ So you have insert Pawan 5 times in a table. 5 is the length(Pawan).

Sample Input

Expected output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script

Use the below script to generate the source table and fill them up with the sample data.

--

CREATE TABLE PrintLengthTimes
(
	Value VARCHAR(100)
)
GO

INSERT INTO PrintLengthTimes Values ('ISHA')
INSERT INTO PrintLengthTimes Values ('PAWAN')

--

SOLUTION 1 | USING XML & CROSS APPLY

--

SELECT SplittedString FROM
(
    SELECT  CAST(REPLICATE(CONCAT('<A>' , Value , '</A>' ),DATALENGTH(Value)) AS XML) Xmlcol FROM PrintLengthTimes
) s
CROSS apply
(
    SELECT ProjectData.D.value('.', 'VARCHAR(100)') as SplittedString
    FROM s.xmlcol.nodes('A') as ProjectData(D)
)a

--

SOLUTION 2 | USING Numbers TABLE & CROSS APPLY

--

SELECT Value OutputChrs FROM PrintLengthTimes 
CROSS APPLY 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(Value) 
)x

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn. Enjoy !!!

Keep Learning

Pawan Khowal 

Http://MSBISkills.com