SQL Server, Create List into 1 column-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.