For XML

Sometimes, when you have master/detail tables you may need to write a query that, for each row in the master table, returns some data along with a compact list of all the details.

For example, in AdventureWorks we have a CountryRegion master table with the CountryRegionCurrency detail and we want to obtain a row set containing all regions and a comma separated list of currencies for that region.

Diagram

Query

SELECT 
 [CountryRegionCode],
 [Name],
 stuff( ( SELECT ', '+ reg.[CurrencyCode] 
          FROM [Sales].[CountryRegionCurrency] AS reg WITH(NOLOCK)      
          WHERE base.[CountryRegionCode]=reg.[CountryRegionCode]
          FOR XML PATH('')),1,2,'') AS [Currencies]
FROM [Person].[CountryRegion] AS base WITH(NOLOCK)
ORDER BY base.[Name]

Result

CountryRegionCode	Currencies	Currencies
AU	Australia	AUD
AT	Austria	ATS, EUR
AZ	Azerbaijan	NULL
BS	Bahamas, The	BSD