Concatenate column value to a string base on Grouping

samples, we have this rows in DB

ID Name Value
1 A 4
1 B 8
2 C 9

We want to group this in to 2 rows group by Id, like this

ID NameValues
1 A:4, B:8
2 C:9

This is 4 solutions for this:

--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)
DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[
        {
            "Id":1,
            "Name":"A",
            "Value":4
        },
        {
            "Id":1,
            "Name":"B",
            "Value":8
        },
        {
            "Id":2,
            "Name":"C",
            "Value":9
        }
    ]
' 
   
SELECT * 
INTO #YourTable
FROM  OPENJSON ( @json )  
WITH (   
    Id  int '$.Id',  
    [Name] varchar(200) '$.Name',  
    [Value] int '$.Value'
) 
select * from #YourTable


--forSQL Server 2017 and later
SELECT  [ID], STRING_AGG( [Name] + ':' +  CAST([Value] AS VARCHAR) , ', ')
FROM #YourTable 
GROUP BY ID

--forSQL Server 2017 before that
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

SELECT 
  [ID],
  STUFF( (
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

SELECT 
  [ID],
  STUFF( (
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH('') ), 1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID


DROP TABLE #YourTable

Without Grouping you can do this for name columns

We wanna select all items like : A:4, B:8, C:9
You can use below SQL

DECLARE @result varchar(max)
select @result = coalesce(@result + ', ', '') + [Name] + ':' +  CAST([Value] AS VARCHAR) from #YourTable
select @result as Names

--or this way in SQL Server 2017 or later
select STRING_AGG( [Name] + ':' +  CAST([Value] AS VARCHAR) , ', ') from #YourTable