Imagine you have a table in MS SQL Server with a column containing many strings in multiple rows. How do you display them concatenated in one line?
Assume you have a table called Person
with a Name
column like the following:
CREATE TABLE Person ( Id INT PRIMARY KEY, Name VARCHAR(255) ); INSERT INTO Person(Id, Name) VALUES (1, 'Amir'), (2, 'Sofia'), (3, 'Aya'), (4, 'Mateo'), (5, 'Leila'), (6, 'Yara'), (7, 'Ndidi'), (8, 'Santiago');
If you want to display all the names in one string and you are using SQL Server after 2017, you can use the STRING_AGG
function to concatenate multiple rows:
SELECT STRING_AGG(Name, ', ') AS Names FROM Person; -- Names -- Amir, Sofia, Aya, Mateo, Leila, Yara, Ndidi, Santiago
If you are using an earlier version of SQL Server you can use XML:
SELECT Name + ', ' AS 'data()' FROM Person FOR XML PATH(''); -- XML_F52E2B61-18A1-11d1-B105-00805F49916B -- Amir, Sofia, Aya, Mateo, Leila, Yara, Ndidi, Santiago
Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.