[Edit]

MS SQL Server - STRING_AGG function example

1 contributors

2 contributions

0 discussions

0 points

Created by:

Mark-Rotteveel

537

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:

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

Practical example

Edit

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:

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

Output:

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

Database preparation

Edit

create_tables.sql file:

xxxxxxxxxx
 
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:

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