SQL Query help needed

Question

Answer 1

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