SQL DISTINCT 在除一列之外的多列上，合并结果中的最后一列

Question

我有一个学生数据库，我需要给每个家庭发送一封信，但地址上必须有姓氏。 同一个家庭中可能有多个姓氏，所以我需要参考每个姓氏，但仍然只向地址发送一封信。 SQL 2014.

示例数据：

+------------+----------------+-------+-------+-------+
|  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 |
+------------+----------------+-------+-------+-------+

我需要返回这个：

+-------------------+----------------+-------+-------+-------+
|  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 |
+-------------------+----------------+-------+-------+-------+

编辑 - 多亏@TanvirArjel我使用了以下最终查询，这比我原来的帖子建议的要复杂一些。 希望这对其他人有所帮助！

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

Answer 1

试试这个：

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