SQL Query to get comma separated values ?

Question

How to get comma separated values.

Sachin Singh · Answer

these are the various techniques https://www.sqlshack.com/converting-commas-or-other-delimiters-to-a-table-or-list-in-sql-server-using-t-sql/

Jignesh Kumar · Answer

Hi Gopi, you can use below query based on SQL server version, DECLARE @Table1 TABLE(ID INT, Name varchar(100)); INSERT INTO @Table1 VALUES (1, 'Jay' ),(1, 'Raj' ),(1, 'Deep' ),(1, 'Nayan' ); SELECT ID ,STUFF((SELECT ', ' + CAST(Name AS VARCHAR(10)) [text()] FROM @Table1 WHERE ID = t.ID FOR XML PATH( '' ), TYPE) .value( '.' , 'NVARCHAR(MAX)' ),1,2, ' ' ) List_Output FROM @Table1 t GROUP BY ID -- if you are using SQL server higher than 2017 you can use below one SELECT ID , STRING_AGG(Name, ', ' ) AS List_Output FROM @Table1 GROUP BY ID;

Uttam Kumar · Answer

There are 3 ways to do this. You can try one by one and choose the one you like it. 1. Concat Rows using COALESCE Select EmpName from Employee; Declare @val Varchar(MAX); Select @val = COALESCE(@val + ', ' + EmpName, EmpName) from Employee Select @val; 2. Concat Rows using FOR XML PATH To remove the leading comma: Select SUBSTRING( ( Select ', ' + EmpName As 'data()' from Employee FOR XML PATH('') ), 2, 9999) As Employees If you don't need the trailing space and just want a comma along a separator, then remove the data() function: Select Employees = STUFF(( Select ', ' + EmpName from Employee FOR XML PATH('')), 1, 1, '') 3. Concat Rows using STRING_AGG NOTE: This is available from SqlServer 2017 onwards Select STRING_AGG(ISNULL(EmpName, ' '), ',') As Employees from Employee

Muhammad Imran Ansari · Answer

If we have the following table: want to get ID column with comma seperated, Use the following query in SQL: DECLARE @Ids VARCHAR (200) SELECT TOP 5 @Ids = COALESCE (@Ids + ',' , '' ) + CAST (id AS VARCHAR (5)) FROM [dbo].[person] SELECT @Ids Output:

SQL Query to get comma separated values ?

Answers (4)