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.
- 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>
- 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>
- 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>
- 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
-
Query Store Collection:
This concatenation of plan_id to fire a single query to collect all query plan information from each database. -
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.