How to concatenate text from multiple rows into a single text string in SQL Server

The Problem

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?

The Solution

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