/ SQL Server

The Complete Beginner's Guide to Understanding the Grouped Concatenations (SQL Server)

Introduction

We all came across situations where we need to concatenate multiple rows for a column into a single string for reporting purposes.

Input
Let us consider an example of Pets Table, which contains OwnerId and PetName columns.

CREATE TABLE Pets (OwnerId INT,PetName VARCHAR(20))
INSERT Pets(OwnerId,PetName)
VALUES 
(1,'Rocky'), (1,'Jackie'),
(2,'Charlie'), (2,'Archie'),
(3,'Oscar'), (3,'Coco'),
(4,'Jasper'), (4,'Shadow')    

SELECT OwnerId, PetName FROM Pets

OwnerId PetName
1 Rocky
1 Jackie
2 Charlie
2 Archie
3 Oscar
3 Coco
4 Jasper
4 Shadow

Pets Table

Output
The output should be grouped by concatenation to know pets contained by each owner flattened as a single row. Here we are using CSV format.

OwnerId PetName
1 Rocky, Jackie
2 Charlie, Archie
3 Oscar, Coco
4 Jasper, Shadow

Grouped Pets Table

Approaches

1. STRING_AGG

Introduced in SQL Server (starting with 2017), it concatenates the values of string expressions and places separator values between them (not added at the end of the string).

Understanding STRINGAGG
Input:

-- STRING_AGG ( expression, separator )
SELECT STRING_AGG ( PetName, ',') AS csv 
FROM Pets

Output:
Rocky,Jackie,Charlie,Archie,Oscar,Coco,Jasper,Shadow

A. Simple output

-- Group by OwnerId
SELECT OwnerId,STRING_AGG ( ISNULL(PetName,'N/A'), ',') AS csv 
FROM Pets
GROUP BY OwnerId

B. Sorted PetNames

-- Within Group Order By Clause
SELECT OwnerId,STRING_AGG ( ISNULL(PetName,'N/A'), ',') WITHIN GROUP (ORDER BY PetName ASC) AS csv
FROM Pets
GROUP BY OwnerId

C. Ignore Null PetName

-- Adding Where Clause
SELECT OwnerId,STRING_AGG ( ISNULL(PetName,'N/A'), ',') AS csv 
FROM Pets
WHERE PetName IS NOT NULL
GROUP BY OwnerId

D. Removing PetName duplicates

-- Adding Distinct Statement
SELECT DistinctPets.OwnerId,
       STRING_AGG ( ISNULL(DistinctPets.PetName,'N/A'), ',') AS csv 
  FROM (
        SELECT DISTINCT OwnerId, PetName 
          FROM Pets 
        ) AS DistinctPets
GROUP BY DistinctPets.OwnerId

For XML Path

One of the most commonly used ways for performing grouped concatenation.

Understanding STUFF Function
Let's learn about STUFF method first.
The STUFF function inserts a string into another string.
Example
Delete 3 characters from the first string, abcdef, starting at position 2, at b, and inserting the 2nd string at the deletion point.
Input
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
Output
aijklmnef

Understanding XML PATH
PATH mode generates XML from a SELECT query.

  1. Wrapping each column value in row element named 'Pets'.

Input

SELECT OwnerId, PetName FROM Pets
FOR XML PATH('Pets')

Output

<Pets>
  <OwnerId>1</OwnerId>
  <PetName>Rocky</PetName>
</Pets>
<Pets>
  <OwnerId>1</OwnerId>
  <PetName>Jackie</PetName>
</Pets>
<Pets>
  <OwnerId>2</OwnerId>
  <PetName>Charlie</PetName>
</Pets>
<Pets>
  <OwnerId>2</OwnerId>
  <PetName>Archie</PetName>
</Pets>
<Pets>
  <OwnerId>3</OwnerId>
  <PetName>Oscar</PetName>
</Pets>
<Pets>
  <OwnerId>3</OwnerId>
  <PetName>Coco</PetName>
</Pets>
<Pets>
  <OwnerId>4</OwnerId>
  <PetName>Jasper</PetName>
</Pets>
<Pets>
  <OwnerId>4</OwnerId>
  <PetName>Shadow</PetName>
</Pets>
  1. Specify Blank Path to remove the row element wrapped around elements

Input

SELECT OwnerId, PetName FROM Pets
FOR XML PATH('')

Output

<OwnerId>1</OwnerId>
<PetName>Rocky</PetName>
<OwnerId>1</OwnerId>
<PetName>Jackie</PetName>
<OwnerId>2</OwnerId>
<PetName>Charlie</PetName>
<OwnerId>2</OwnerId>
<PetName>Archie</PetName>
<OwnerId>3</OwnerId>
<PetName>Oscar</PetName>
<OwnerId>3</OwnerId>
<PetName>Coco</PetName>
<OwnerId>4</OwnerId>
<PetName>Jasper</PetName>
<OwnerId>4</OwnerId>
<PetName>Shadow</PetName>
  1. Specify Blank Path to remove the row element wrapped around elements and select only PetName Column

Input

SELECT PetName FROM Pets
FOR XML PATH('')

Output

<PetName>Rocky</PetName>
<PetName>Jackie</PetName>
<PetName>Charlie</PetName>
<PetName>Archie</PetName>
<PetName>Oscar</PetName>
<PetName>Coco</PetName>
<PetName>Jasper</PetName>
<PetName>Shadow</PetName>
  1. Perform any string operations to produce output string without wrapped element

Input

SELECT ' ' + PetName FROM Pets
FOR XML PATH('')

Output

 Rocky Jackie Charlie Archie Oscar Coco Jasper Shadow Rocky Jackie

Simple Output

SELECT DISTINCT 
    OwnerId, 
    STUFF(( SELECT ',' + PetName
              FROM Pets P1 
              WHERE P1.OwnerId=P2.OwnerId FOR XML PATH('')),1,1,'')
    FROM Pets P2

Petnames with XML encoded values

-- Treats XML values as strings instead of entities
SELECT DISTINCT 
    OwnerId, 
    STUFF
    (
      ( 
        SELECT ',' + PetName
        FROM Pets P1 
        WHERE P1.OwnerId=P2.OwnerId 
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)') ,1,1,'')
FROM Pets P2
GROUP BY OwnerId

Sorted Names

-- Treats XML values as strings instead of entities
SELECT DISTINCT 
    OwnerId, 
    STUFF
    (
      ( 
        SELECT ',' + PetName
        FROM Pets P1 
        WHERE P1.OwnerId=P2.OwnerId 
        ORDER BY P1.PetName
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)') ,1,1,'')
FROM Pets P2
GROUP BY OwnerId

Removing Petname Duplicates

SELECT DISTINCT 
    OwnerId, 
    STUFF
    (
      ( 
        SELECT ',' + PetName
        FROM Pets P1 
        WHERE P1.OwnerId=P2.OwnerId 
        GROUP BY P1.PetName
        FOR XML PATH(''), TYPE
    ).value('.[1]', 'VARCHAR(MAX)') ,1,1,'')
FROM Pets P2
GROUP BY OwnerId

Others

There are other performance massive ways of using User Defined Functions, Cursors and CTE

Practical Usages

  1. Query Store Collection:
    This concatenation of plan_id to fire a single query to collect all query plan information from each database.

  2. Index Data Collection:
    Combine Indexes on Tables present in each database. Collect and concatenate all the indexes data grouped by the table and grouped by a database.

The Complete Beginner's Guide to Understanding the Grouped Concatenations (SQL Server)
Share this

Subscribe to Coding Today

Popular queries