Tags

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


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

Col1 Col2 Col3
A 1 2
A 3 4
B 5 6

Expected Output

Col1 Cols
A 1, 3, 2, 4
B 5, 6

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

Advertisements