Multiple rows to one comma-separated value in Sql Server

0 votes

To return data as comma separated values, I want to develop a table valued function in SQL Server.

For example table: tbl

ID | Value
---+-------
 1 | 100
 1 | 200
 1 | 300     
 1 | 400 

Now when I execute the query using the function Func1(value)

SELECT Func1(Value) 
FROM tbl 
WHERE ID = 1

Output that I want is: 100,200,300,400

Can someone please help me with this?

Sep 16, 2022 in Database by Kithuzzz
• 38,010 points
37,008 views

1 answer to this question.

0 votes

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value 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

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

QL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);


SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

If you need to know more about SQL, it is recommended to go for the SQL Certification today.

answered Sep 17, 2022 by narikkadan
• 63,420 points

Related Questions In Database

0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

You can use the PIVOT function to ...READ MORE

answered Feb 8, 2022 in Database by Vaani
• 7,020 points
1,308 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

CTEs and ROW_NUMBER can be combined together which will ...READ MORE

answered Feb 10, 2022 in Database by Vaani
• 7,020 points
590 views
0 votes
0 answers

How to ALTER multiple columns at once in SQL Server

ALTER the data types of several columns in ...READ MORE

Aug 12, 2022 in Database by Kithuzzz
• 38,010 points
2,223 views
0 votes
0 answers

How to Create a real one-to-one relationship in SQL Server?

I have two tables, Country and Capital, ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 38,010 points
319 views
0 votes
0 answers

How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows: Emp_cd ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,010 points
1,354 views
0 votes
0 answers

How to update two tables in one statement in SQL Server 2005?

In one operation, I want to update ...READ MORE

Aug 20, 2022 in Database by Kithuzzz
• 38,010 points
457 views
0 votes
0 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

In one ALTER TABLE statement, I would ...READ MORE

Sep 2, 2022 in Database by Kithuzzz
• 38,010 points
502 views
0 votes
0 answers

Efficiently convert rows to columns in sql server

I need to deal with a lot ...READ MORE

Sep 3, 2022 in Database by Kithuzzz
• 38,010 points
756 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,420 points
2,044 views
0 votes
1 answer

How to delete multiple rows in SQL where id = (x to y)

If you need to delete based on ...READ MORE

answered Sep 17, 2022 in Database by narikkadan
• 63,420 points
4,848 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP