4,707 questions
Next time you ask a question, please include CREATE TABLE statements for your tables and INSERT statements with sample data. And most of all, don't post images; we cannot copy and paste from images.
Had you posted CREATE TABLE + INSERT, it would have been possible to test my solutions. The below is untested.
SQL 2017 and later, including SQL Azure:
SELECT id, string_agg(name, ','), string_agg(ciy, ',')
FROM tbl
GROUP BY id
Earlier versions:
; WITH CTE AS (
SELECT a.id, b.name.value('.', 'nvarchar(MAX)') AS name,
b.city.value('.', 'nvarchar(MAX)') AS city
FROM (SELECT DISTINCT id FROM tbl) AS a
CROSS APPLY (SELECT b.name + ','
FROM tbl b
WHERE a.id = b.id
FOR XML PATH(''), TYPE) AS b(name)
CROSS APPLY (SELECT c.city + ','
FROM tbl c
WHERE a.id = c.id
FOR XML PATH(''), TYPE) AS c(city)
)
SELECT a.id, substring(b.name, 1, len(b.name)-1),
substring(c.city, 1, len(c.name)-1)
FROM CTE