How to use STRING AGG in Sql Server

Intro

SQL Server provides the STRING_AGG function to concatenate rows of strings when aggregating or grouping rows. In this article, we will learn how to use STRING_AGG in SQL Server.

The Syntax

The basic syntax of a STRING_AGG is as follows:

STRING_AGG (string, delimiter) [order_clause]

• string: is the column to group or aggregate
• delimiter: is the separator to use (such as comma)
• order clause: is a sql statement allowing to order the strings that are grouped.

The order clause has the following format.

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Creating a Table

We start by creating a table of employees with emails and departments.

CREATE TABLE employees (
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    email VARCHAR (50) NOT NULL,
    department VARCHAR (50) NOT NULL,
);

insert into employees (first_name, last_name, email, department) 
	values 
	('Keith', 'Holliday', 'test2@test.com', 'sales'),
	('Jon', 'Doe', 'test@test.com', 'sales'),
	('Jane', 'Doe', 'test3@test.com', 'marketing');

A Simple Example

In our first example we will group emails into single rows, separated by commas. Each group will be by department. This is helpful if we need to build email lists by department.

SELECT
    department, 
    STRING_AGG(email,',') email_list
FROM
    employees e 
GROUP BY
    department;

Our second example will sort the emails. Notice in the above results, the sales emails are not sorted.

SELECT
    department, 
    STRING_AGG(email,',')
    	 WITHIN GROUP (ORDER BY email) as email_list
FROM
    employees e 
GROUP BY
    department;