Tags

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


T-SQL Query | [ Multiple Comma Separated Values Puzzle]  – In this puzzle we have to remove the comma separated value with the single value. Please check out the sample input and expected output for details.

Sample Input

ID Name
1 a,b,c,d,e
2 a,b
3 c,d
4 e
5 f

Expected Output

ID Value
1 A
1 B
1 C
1 D
1 E
2 A
2 B
3 C
3 D
4 E
5 F

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 testMultipleValues
(
ID int
,Name varchar(100)
)
GO

--Insert Data
INSERT INTO testMultipleValues(ID,Name)
VALUES
(1,'a,b,c,d,e'),
(2,'a,b'),
(3,'c,d'),
(4,'e'),
(5,'f')

--Verify Data
SELECT ID, Name FROM testMultipleValues

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT ID,t.value('.', 'VARCHAR(10)') Value FROM 
(	
	SELECT ID, CAST('<A>'+ REPLACE(Name,',','</A><A>')+ '</A>' AS XML) AS x FROM testMultipleValues
) O
CROSS APPLY x.nodes('/A')x(t)

--

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

Keep Learning

http://MSBISkills.com