SQL Server, Create List into 1 column

Question

In SQL Server, Want to create a list from a query into 1 column.

IF OBJECT_ID('tempdb..#PatientMeds') IS NOT NULL
    DROP TABLE #PatientMeds


Create Table #PatientMeds (name varchar(50), medication varchar(50))

Insert INTO #PatientMeds (name, medication)
values
('Patient 1', 'Med 1'),
('Patient 1', 'Med 2'),
('Patient 2', 'Med 1'),
('Patient 2', 'Med 2'),
('Patient 2', 'Med 3')

Table:

name    medication
Patient 1   Med 1
Patient 1   Med 2
Patient 2   Med 1
Patient 2   Med 2
Patient 2   Med 3

Desired Output:

name    medication
Patient 1   Med 1, Med 2
Patient 2   Med 1, Med 2, Med 3

There are no lists or arrays in SQL Server. You can get a comma-separated string at best. — Panagiotis Kanavos, Oct 05 '21 at 12:29
Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Charlieface, Oct 05 '21 at 13:16

score 3 · Answer 1 · answered Oct 05 '21 at 12:29

3

You can use string_agg() :

select name, string_agg(medication, ',') within group (order by medication) as medication
from #PatientMeds
group by name;

Note : If you are with some older versions then you may look xml approach.

answered Oct 05 '21 at 12:29

Yogesh Sharma

49,870
5
26
52

score 0 · Accepted Answer · edited Mar 03 '23 at 12:53

There are no arrays in T-SQL. Now that even SQL Server 2016 is out of mainstream support, one can say that STRING_AGG is available in all SQL Server versions still in mainstream support. Using it you can write just

SELECT 
    name,
    STRING_AGG(medication, ', ') WITHIN GROUP (ORDER BY medication) as medication
FROM ThatTable
GROUP BY name

Without WITHIN GROUP SQL Server is free to return the results in any order.

SQL Server, Create List into 1 column

2 Answers2