Comma-separated values to rows with sum in SQL Server

create table #t(org varchar(50), area int)

insert into #t values ('a', 500), ('b', 200), ('c', 400)

select * from #t

Output:

org     area
--------------
a       500
b       200
c       400

I want to convert the output to this format:

org         area
-----------------
a,b,c       1100

i.e. comma-separated all the orgs and sum of it’s area value.

Answer

Sub-query is really not necessary :

select stuff((select ','+t1.org from #t t1 for xml path('')), 1, 1, '') as org, 
       sum(area) as area
from #t t;