The String_Agg function can be used to convert data from rows into a single delimited string.
The detailed explanation is provided in the below video tutorial.
The example uses the Projects table. Please find below the Create script for the table
1- Create table - Projects
SQL
CREATE TABLE [dbo].[Projects](
[ProjectID] [smallint] NOT NULL,
[ProjectName] [nvarchar](250) NULL,
[ManagerName] [nvarchar](250) NOT NULL
)
2- Insert sample data
SQL
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (100, 'Data Quality', 'Tom');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (101, 'Data Warehouse', 'Jen');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (102, 'Profiling', 'Brad');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (103, 'Data Lake', 'Brad');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (104, 'Data Analytics', 'Amy');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (105, 'Reporting', 'Amy');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (106, 'Lineage', 'Tom');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (107, 'Management', 'Matt');
INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ManagerName])
VALUES (108, 'Big Data', 'Tom');
INSERT INTO [dbo].[Projects] ([ProjectID],[ManagerName])
VALUES (109, 'Tom');
We are now going to write a query to find all the projects managed by each manager.
We are going to use '|' as a delimiter. Any other delimiter can be used as well.
Query
Select ManagerName, STRING_AGG(ProjectName, ' , ')
from
dbo.Projects
group by ManagerName
Output -
We are now going to use the WITHIN GROUP clause to order the concatenated values in ascending order. For descending order, specify DESC.
Select ManagerName, STRING_AGG(ProjectName, ' , ')
within group (order by ProjectName )
from
dbo.Projects
group by ManagerName
Output -
Please note that any NULL values in the data will be ignored and will not be a part of the concatenated string.