Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [Covert Comma Separated Values to a Table using Cross Apply & XML Puzzle ]  – In this puzzle we have to get comma separated values into a table format using XML and Cross Apply operators. Please check out the sample input and expected output for details.

Sample Input

ID VALUE
1 a,b,c
2 s,t,u,v,w,x

Expected Output

Id SplitedValue
1 a
1 b
1 c
2 s
2 t
2 u
2 v
2 w
2 x

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

CREATE TABLE TestCommaUsingCrossApply
(
ID INT
,VALUE VARCHAR(100)
)
GO

--Insert Data

INSERT INTO TestCommaUsingCrossApply(ID,VALUE)
VALUES
(1,'a,b,c'),
(2,'s,t,u,v,w,x')

--Verify Data
select ID,VALUE from TestCommaUsingCrossApply

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


SELECT Id,SplitedValue FROM
(
    SELECT ID,cast(('<X>'+replace(e.VALUE,',' ,'</X><X>')+'</X>') as xml) as xmlcol  FROM  TestCommaUsingCrossApply e 
) s
OUTER APPLY
(
    SELECT ProjectData.D.value('.', 'varchar(100)') as SplitedValue
    FROM s.xmlcol.nodes('X') as ProjectData(D)
) a 



--

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

Keep Learning

http://MSBISkills.com