How to convert an integer column as a comma separated row in Azure sql

Question

Anyone can help please I want to Convert sql table column to a comma separated row in azure sql I have already tried this code but getting error that can not convert to varchar please suggest

DECLARE @Country TABLE (id INT, [Name] Varchar(30) )

INSERT INTO @Country VALUES (1,'India')
INSERT INTO @Country VALUES (2,'USA')
INSERT INTO @Country VALUES (3,'Japan')
INSERT INTO @Country VALUES (4,'China')
INSERT INTO @Country VALUES (5,'Switzerland')

SELECT STUFF( -- Remove first comma
(SELECT  ', ' + ID FROM -- create comma separated values
(SELECT ID FROM @Country --Your query here
) AS T FOR XML PATH('')
)
,1,1,'') AS [Name]

Lukasz Szozda · Accepted Answer · 2019-06-17 19:55:49Z

1

You could use STRING_AGG:

SELECT STRING_AGG(ID, ', ') FROM @Country

db<>fiddle demo

XML + STUFF is old way when there was no GROUP_CONCAT equivalent available.

answered Jun 17, 2019 at 19:55

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2019-06-17 19:53:55Z

1

You need CAST the integer

SELECT  ', ' + CAST(ID as nvarchar(max)) FROM

answered Jun 17, 2019 at 19:53

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

How to convert an integer column as a comma separated row in Azure sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related