How does Partitioning By a Substring in T-SQL Work?

Question

I found the perfect example while browsing through sites of what I'm looking for. In this code example, all country names that appear in long formatted rows are concatenated together into one result, with a comma and space between each country.

Select CountryName from Application.Countries; 
 
Select SUBSTRING( 
( 
     SELECT ',' + CountryName AS 'data()'
         FROM Application.Countries FOR XML PATH('') 
), 2 , 9999) As Countries

Source: https://www.mytecbits.com/microsoft/sql-server/concatenate-multiple-rows-into-single-string

My question is: how can you partition these results with a second column that would read as "Continent" in such a way that each country would appear within its respective continent? The theoretical "OVER (PARTITION BY Continent)" in this example would not work without an aggregate function before it. Perhaps there is a better way to accomplish this? Thanks.

Answer 1

Use a continents table (you seem not to have one, so derive one with distinct), and then use the same code in a cross apply using the where as a "join" condition:

select *
from
(
    select distinct continent from Application.Countries
) t1
cross apply
(
    Select SUBSTRING( 
    ( 
        SELECT ',' + CountryName AS 'data()'
        FROM Application.Countries as c FOR XML PATH('') 
        where c.continent=t1.continent
    ), 2 , 9999) As Countries
) t2

Note that it is more usual, and arguably has more finesse, to use stuff(x,1,1,'')instead of substring(x,2,9999) to remove the first comma.