SQL Puzzle | The String Puzzle – combine data by one column and then another one using comma

Here you have given a table with three columns – Col1, Col2, Col3. We have to group data based on Col1. Based on Col1 group combine col2 values using a comma and then combine col3 values using a comma

Please check out the sample input and the expected output below-

Sample Input

Expected Output

Script | use below script to create table & insert some sample data

--

CREATE TABLE One
(
	 Col1 VARCHAR(1)
	,Col2 VARCHAR(1)
	,Col3 VARCHAR(1)
)
GO

INSERT INTO One VALUES
('A', 1, 2),
('A', 3, 4),
('B', 5, 6)
GO



--

Solution 1 | XML & Stuff

--


SELECT Col1 , STUFF 
				(
					(
						SELECT ', ' + a
						FROM (	
								SELECT Col1,Col2 a,1 Id
								FROM One
								UNION ALL
								SELECT Col1,Col3 a,2 Id
								FROM One
							 )a
						WHERE a.Col1 = r.Col1 
						ORDER BY a.Id				
					FOR XML PATH('')
					) 
				,1,2,''
				) Cols
FROM One r
GROUP BY Col1


--

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