SQL PUZZLE | Generate a comma separated list XML | Advance SQL

I got an email yesterday asking about this so I checked my wesbite for this article and understand that I have never blogged about this. So I thought why wait let’s share it today itself :). In this puzzle you have to read all the data from Vals column and get us a comma separated list of values from that column. Please see the sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE myData2
(
	 Id INT
	,Vals VARCHAR(100)
)
Go

INSERT INTO myData2 VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d')
GO

SELECT * FROM myData2
GO

--

SOLUTION – 1

--

SELECT SUBSTRING((SELECT ','+Vals 
FROM myData2
FOR XML PATH('')),2,1000)

--

Output – 1

--

------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 2

--

SELECT STUFF((SELECT ','+Vals 
FROM myData2
FOR XML PATH('')), 1, 1, '')

--

Output – 2

--

------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 3

--

SELECT REPLACE(
	(
		SELECT Vals as 'data()' 
		FROM myData2 FOR XML PATH('')
	)
, ' ', ',')

--

Output – 3

--


------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 4 | Applicable for SQL Server 2017

--

SELECT STRING_AGG(Vals, ',')
FROM myData2

--

Output – 4

--


------------------
a,b,c,d

(1 row affected)



--

Reference –

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com