SQL Puzzle | Split String Puzzle – II (Linked In)

Puzzle Statement

This puzzle was asked in social media ( Linked In ), In this puzzle you have to split a string and insert in the same column. Basically here the number will be increased based on the id column.

SQL Puzzle – Linked In

Check out the input and output required for this puzzle is-

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.

UPDATE – 6-Oct-2015 – Solution #1

--

SELECT ID, SplittedString FROM
(
	SELECT  ID, CAST('<A>'+ REPLACE(Value,',','</A><A>')+ '</A>' AS XML) Xmlcol FROM SplitString2
) s
CROSS apply
(
	SELECT ProjectData.D.value('.', 'VARCHAR(5)') as SplittedString
	FROM s.xmlcol.nodes('A') as ProjectData(D)
)a

--

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

Keep Learning

Pawan Khowal 

Http://MSBISkills.com