SQL Query help needed

asked 2021-11-14T11:56:06.85+00:00
Santosh Kumar 61 Reputation points

149143-image.png

No comments
{count} votes

Accepted answer
  1. answered 2021-11-14T13:50:48.73+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

    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
    
    No comments

0 additional answers

Sort by: Most helpful