SQL DISTINCT on multiple columns except one, combine last column in result

Question

I have a database of students and I need to send one letter to each household but have to have the last name on the address. There could be multiple last names in the same household so I would need to reference each but still only send one letter to the address. SQL 2014.

Sample Data:

    +------------+----------------+-------+-------+-------+
    |  LastName  |   Address 1    | City  | State |  Zip  |
    +------------+----------------+-------+-------+-------+
    |  Smith     | 123 Fake St.   |  NY   |  NY   | 12345 |
    |  Jones     | 123 Fake St.   |  NY   |  NY   | 12345 |
    |  Ball      | 123 North St.  |  NY   |  NY   | 12345 |
    |  Wood      | 123 South St.  |  NY   |  NY   | 12345 |
    |  Ball      | 123 South St.  |  NY   |  NY   | 12345 |
    +------------+----------------+-------+-------+-------+

I need to return this:

    +-------------------+----------------+-------+-------+-------+
    |  LastName         |   Address 1    | City  | State |  Zip  |
    +-------------------+----------------+-------+-------+-------+
    |  Smith & Jones    | 123 Fake St.   |  NY   |  NY   | 12345 |
    |  Ball             | 123 North St.  |  NY   |  NY   | 12345 |
    |  Wood & Ball      | 123 South St.  |  NY   |  NY   | 12345 |
    +-------------------+----------------+-------+-------+-------+

EDIT - Thanks to @TanvirArjel I used the following final query which is a bit more complicated than my original post would suggest. Hope this helps others!

    SELECT STUFF((
        SELECT DISTINCT ',' + LastName
        FROM Users
        WHERE (
                CASE 
                    WHEN Student_MailUnitNumber IS NULL
                        THEN CONCAT (
                                Student_MailStreetNumber
                                ,' '
                                ,Student_MailStreetName
                                )
                    ELSE CONCAT (
                            Student_MailStreetNumber
                            ,' '
                            ,Student_MailStreetName
                            ,', '
                            ,Student_MailUnitNumber
                            )
                    END
                ) = a.Address1
            AND City = a.City
            AND [State] = a.[State]
            AND Zip = a.Zip
        FOR XML PATH('')
        ), 1, 1, '') AS LastName
,Address1
,City
,STATE
,Zip
    FROM (
SELECT LastName
    ,CASE 
        WHEN Student_MailUnitNumber IS NULL
            THEN CONCAT (
                    Student_MailStreetNumber
                    ,' '
                    ,Student_MailStreetName
                    )
        ELSE CONCAT (
                Student_MailStreetNumber
                ,' '
                ,Student_MailStreetName
                ,', '
                ,Student_MailUnitNumber
                )
        END AS [Address1]
    ,Student_MailCity AS [City]
    ,Student_MailState AS [State]
    ,Student_MailZip AS [Zip]
FROM users
WHERE usertype = 'Student'
    AND isActive = 1
    AND PriBuilding IS NOT NULL
    AND student_grade NOT LIKE '%K%'
    AND Student_Grade <> '1'
) AS A
    GROUP BY Address1
,City
,STATE
,Zip

https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — jarlh, Aug 23 '18 at 13:58

score 3 · Accepted Answer · answered Aug 23 '18 at 14:05

Try this:

SELECT Address1,City,State,Zip,
     STUFF(
         (SELECT DISTINCT ',' + LastName
          FROM TableName
          WHERE Address1 = a.Address1 AND City= a.City AND State = a.State and Zip = a.Sip
          FOR XML PATH (''))
          , 1, 1, '')  AS LastName
FROM TableName AS a
GROUP BY Address1, City,State,Zip

SQL DISTINCT on multiple columns except one, combine last column in result

1 Answers1