How to get column values in one comma separated value in another column

Question

Hi Team - This is the output of my query :

And this is how I am expecting the output:

This is the simple select query :

SELECT
--p.title Program, at.title AssistanceType,
distinct pt.firstname
,pt.LastName
,b.pntid as ' ID',
--EligibilityBeginDate = CONVERT(VARCHAR(10), b.EligibilityBeginDate, 101),
--ApprovalDate = CONVERT(VARCHAR(10), b.ApprovalDate, 101),
InactiveDate = CONVERT(VARCHAR(10), b.LatestEventDate, 101),
InactiveReason = ApplicationLatestStatusReasonTitle,
b.AppCreatedatetime as 'Application Date'
, b.referralsourcec as referralsource
,Case when InactiveType = 2 then 'Assistance' else 'Application' end as InactiveType
,at.Title as Assistance

FROM #PStatus b  
	LEFT JOIN program p				ON p.programid = b.programid   
	LEFT JOIN assistancetype at		ON at.assistaeid = b.assistypeid   
	left join pati pt			on b.pattid = pt.pattid  
WHERE	1=1  
Order By InactiveType  

----------

Could you please help me with this. Thanks

Answer 1

Hi @kkran ,

As mentioned by other experts, you could use STRING_AGG, STUFF ...FOR XML PATH or other methods.

Please refer below example:

create table TableK  
(FirstName varchar(10),  
LastName varchar(10),  
ID int,  
Date date,  
Reason varchar(20),  
Date1 date,  
source varchar(20),  
Type varchar(20),  
Assistance varchar(100))  
  
insert into TableK values  
('Ava','Tom',1619,'02/10/2021','Not Met','2020-02-19','Solutions','Assistance','Copay'),  
('Ava','Tom',1619,'02/10/2021','Not Met','2020-02-19','Solutions','Assistance','Premium'),  
('Ava','Tom',1619,'02/10/2021','Not Met','2020-02-19','Solutions','Assistance','Travel Expense'),  
('Don','Joe',736,'01/07/2021',' Ended','2019-01-25','Solutions','Assistance','Ancillary Services'),  
('Don','Joe',736,'01/07/2021','Ended','2019-01-25',' Solutions','Assistance','Copay'),  
('Don','Joe',736,'01/07/2021','Ended','2019-01-25','Solutions','Assistance','Travel Expense'),  
('Baby','Shark',5086,'01/07/2021','Assistance','2019-01-31','Solutions','Assistance','Copay'),  
('Baby','Shark',5086,'01/07/2021','Assistance','2019-01-31','Solutions','Assistance','Infusion and Nursing Services')  
  
--Method 1, apply to SQL Server 2017 and later  
select FirstName,LastName,ID,Date,Reason,Date1,source,type  
,STRING_AGG(Assistance,',') Assistance  
from TableK  
group by FirstName,LastName,ID,Date,Reason,Date1,source,type  
  
--Method 2, apply to SQL Server 2016 and earlier  
select distinct FirstName,LastName,ID,Date,Reason,Date1,source,type  
,STUFF((SELECT ',' + Assistance  
        FROM TableK a  
 where a.id=b.id  
        FOR XML PATH('')  
        ), 1, 1, '') Assistance  
from TableK b  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As Tom says, you can use string_agg, if you are on SQL 2017 or later.

If you are on earlier versions you can use FOR XML PATH, which is far less intuitive, but it works. Here is a sample query:

; WITH CTE AS (
   SELECT orderid, p.products.value('.', 'nvarchar(MAX)') AS products
   FROM   orders o
   CROSS  APPLY (SELECT od.prodid + ','
                 FROM   orderdetails od
                 WHERE  o.orderid = od.orderid
                 ORDER  BY od.prodid
                 FOR XML PATH(''), TYPE) AS p(products)
)
SELECT orderid, substring(products, 1, len(products) - 1)
FROM   CTE

Answer 3

Use "STRING_AGG":

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15