How to use GROUP BY to concatenate strings in SQL Server?

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)



INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)



SELECT 

  [ID],

  STUFF((

    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 

    FROM #YourTable 

    WHERE (ID = Results.ID) 

    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')

  ,1,2,'') AS NameValues

FROM #YourTable Results

GROUP BY ID



DROP TABLE #YourTable

select id, string_agg(concat(name, ':', [value]), ', ')

from #YourTable 

group by id

---- test data

declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))

insert @t select 1125439       ,'CKT','Approved'

insert @t select 1125439       ,'RENO','Approved'

insert @t select 1134691       ,'CKT','Approved'

insert @t select 1134691       ,'RENO','Approved'

insert @t select 1134691       ,'pn','Approved'



---- actual query

;with cte(outputid,combined,rn)

as

(

  select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)

  from @t

)

,cte2(outputid,finalstatus,rn)

as

(

select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1

union all

select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1

from cte2

inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1

)

select outputid, MAX(finalstatus) from cte2 group by outputid