Tags

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


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

Id Vals
1 a
2 b
3 c
4 d

Expected output

(No column name)
a,b,c,d

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 –

https://msbiskills.com/2018/01/10/new-t-sql-features-in-sql-server-2017-iii-string_agg/
https://davegugg.wordpress.com/2015/01/29/creating-a-comma-separated-list-with-for-xml-path-and-stuff/

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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

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

Advertisements