EN
MS SQL Server - concatenate multiple rows into one field
0 points
In this article, we would like to show you how to concatenate multiple rows into one field in MS SQL Server.
Quick solution:
xxxxxxxxxx1
SELECT [column1],2
STRING_AGG([column2], 'separator')3
FROM [table_name]4
GROUP BY [column1];To show how to combine multiple rows into one field, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we want to display all the colors and a list of people who like each color in one field.
Query:
xxxxxxxxxx1
SELECT [favorite_color],2
STRING_AGG([name], ', ') AS 'people'3
FROM [users]4
GROUP BY [favorite_color];Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE [users] (2
[id] INT IDENTITY(1,1),3
[name] VARCHAR(50) NOT NULL,4
[favorite_color] VARCHAR(100) NOT NULL,5
PRIMARY KEY ([id])6
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO [users]2
( [name], [favorite_color])3
VALUES4
('Tom', 'red'),5
('Chris', 'green'),6
('Kate', 'blue'),7
('Jack', 'green'),8
('Mark', 'green'),9
('Ann', 'orange'),10
('Natalie', 'pink');