Languages
[Edit]
EN

MS SQL Server - STRING_AGG function example

0 points
Created by:
Mark-Rotteveel
327

In this article, we would like to show you how to use STRING_AGG function in MS SQL Server.
This function is used for concatenating multiple rows into one field with a specified separator.

Quick solution:

SELECT [column1],
   STRING_AGG([column2], 'separator')
FROM [table_name];

Practical example

In this example, we want to display all the colors and a list of people who like each color in one field.

MS SQL Server - STRING_AGG function example
Data used in the example - HeidiSQL

Note:

At the end of this article you can find database preparation SQL queries.

Query:

SELECT [favorite_color],
   STRING_AGG([name], ', ') AS 'people'
FROM [users]
GROUP BY [favorite_color];

Output:

MS SQL Server - concatenate multiple rows into one field - result
MS SQL Server - concatenate multiple rows into one field - results

Database preparation

create_tables.sql file:

CREATE TABLE [users] (
	[id] INT IDENTITY(1,1),
	[name] VARCHAR(50) NOT NULL,
	[favorite_color] VARCHAR(100) NOT NULL,
	PRIMARY KEY ([id])
);

insert_data.sql file:

INSERT INTO [users]
	( [name], [favorite_color])
VALUES
	('Tom', 'red'),
	('Chris', 'green'),
	('Kate', 'blue'),
	('Jack', 'green'),
	('Mark', 'green'),
	('Ann', 'orange'),
	('Natalie', 'pink');

MS SQL Server - functions

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

â€ïžđŸ’» 🙂

Join