A useful bit of syntax in the case that you end up with multiple levels of granularity in a dataset, and you want to collapse it back down. One solution would be to turn your rows into columns (pivoting), or put your row values into a single column separated by commas (or pipes, etc.). Below I'm using the AdventureWorks2017 database to show how to do the latter.
Here it produces a gigantic list of countries with associated state/provinces. So let's collapse that down...
SELECT DISTINCT ',' + subQ.StateProvinceCode
FROM [AdventureWorks2017].[Person].[StateProvince] subQ
WHERE outerQ.CountryRegionCode = subQ.CountryRegionCode
FOR XML PATH('')
), 1, 1, ''
) as LastNameList
FROM [AdventureWorks2017].[Person].[StateProvince] outerQ
GROUP BY outerQ.[CountryRegionCode];