How to concatenate text from multiple rows in SQL

Overview

In this shot, we’ll look at how to concatenate multiple rows of a column together with a delimiter.

For example, consider the following table:

Name Age
Sam 22
John 24
Gabe 26

After concatenating all the rows from the Name column, the output should look as follows:

Concatenated Name
Sam, John, Gabe

The string_agg() function

The string_agg() function allows us to concatenate a list of strings with the specified separator in between.

Syntax

STRING_AGG(expression, separator [order_by_clause])

Parameters

  • expression: This can be any valid expression that results in a character string.

  • separator: This is the separator to use to concatenate the strings.

  • order_by_clause: This is an optional clause that helps in the ordering of the concatenated results.

Code example 1

CREATE TABLE Person (
    Name 	varchar(100) NOT NULL,
    Age 	int
);
INSERT INTO Person 
    (Name, Age) 
  VALUES
    ('George', 20),
    ('Emma', 22),
    ('Harry',15),
    ('Ava',17),
    ('Olivia',25),
    ('Thomas',23);
SELECT STRING_AGG(Name, ', ') as "Concatenated Name" from Person;

Explanation

  • Lines 2–5: We create a table Person using the CREATE TABLE query.

  • Lines 7–15: We insert some records using the INSERT query.

  • Line 17: We use the STRING_AGG query to concatenate the rows of the column Name into a single string.

Coding example 2

CREATE TABLE Person (
    Name 	varchar(100) NOT NULL,
    Age 	int
);
INSERT INTO Person 
    (Name, Age) 
  VALUES
    ('George', 20),
    ('Emma', 22),
    ('Harry',15),
    ('Ava',17),
    ('Olivia',25),
    ('Thomas',23);
SELECT STRING_AGG(Name, ', ' ORDER BY Age DESC) as "Concatenated Name" from Person;

Explanation

  • Lines 2–5: We create a table Person using the CREATE TABLE query.

  • Lines 7–15: We insert some records using the INSERT query.

  • Line 17: We use the STRING_AGG query to concatenate the rows of the column Name into a single string. Here, we use the ORDER BY clause to specify the order in which the rows are concatenated. As per the value (Age) that we pass, the rows are ordered by the Age column in descending order before they are concatenated into a single string.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)