SQL Query Group by Clause

Rahul Aggarwal

I am facing a problem with a SQL query. This is my table structure:

DECLARE @tab TABLE
             (
                   Id INT IDENTITY, 
                   Rid INT, 
                   Address1 VARCHAR(50),
                   City VARCHAR(20)
              ) 

INSERT INTO @tab VALUES (56, 'Test1', 'New York')
INSERT INTO @tab VALUES (1253, 'Test1', 'Delhi')
INSERT INTO @tab VALUES (56, 'Address5', 'Cali')
INSERT INTO @tab VALUES (1253, 'Address5', 'Delhi')

SELECT * FROM @tab

My query:

SELECT
    Address1, STUFF((SELECT ',' + CONVERT(Varchar, Id)
                     FROM @tab TR
                     WHERE TR.Rid IN (56, 1253)
                     GROUP BY Id, Rid, Address1 
                     FOR XML PATH('')), 1, 1, '') AS addid
FROM
    @tab T
WHERE 
    T.Rid IN (56,1253)
GROUP BY
    T.Address1

It is showing me all the Ids in comma wise, while I want to show the Ids Address wise like 1,2 in the first column and 3,4 in the second column, i.e. Ids should be group by Address1.

Thanks

Phong

You need to filter TR.Address1 = T.Address1 instead of TR.Rid IN (56, 1253)

SELECT
    Address1, STUFF((SELECT ',' + CONVERT(Varchar, Id)
                     FROM @tab TR
                     WHERE TR.Address1 = T.Address1 // Adjust the condition here
                     GROUP BY Id, Rid, Address1 
                     FOR XML PATH('')), 1, 1, '') AS addid
FROM
    @tab T
WHERE 
    T.Rid IN (56,1253)
GROUP BY
    T.Address1

Live demo here