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.
SELECT [CountryRegionCode] ,StateProvinceCode FROM [AdventureWorks2017].[Person].[StateProvince]
Here it produces a gigantic list of countries with associated state/provinces. So let’s collapse that down…
SELECT outerQ.[CountryRegionCode], STUFF(( 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];