FOR XML PATH columns - concatenate every consecutive occurrence of each value to another FOR XML PATH field

Question

Currently my result set is defined by this statement

 ISNULL('| ' + [Contry],'') + ISNULL('| ' + [State],'') + ISNULL('| ' + [City],'')   
 AS ProjectLocation

Current output of the "Place" filed shown above looks like this:

United States, United States, United States| California, Hawaii, Virginia| Norfolk, Pearl Harbor, San Diego,

I need the output look like:

United States,  Hawaii,  Pearl Harbor |  United States,  Virginia, Norfolk |  United States, California,  San Diego

The values of the columns called above are defined based of FOR XML PATH('') function as follows:

WITH CTE AS 
(
    SELECT 
        (SELECT Country.CountryName  + ', '
         FROM MyDB.dbo.Country c
         INNER JOIN MyDB.dbo.Contract con ON c.CountryID = con.ContryID 
                                          AND Opp.OppID = Con.OppID 
         FOR XML PATH('')) AS Country,  
        (SELECT State.StateName + ', ' 
         FROM MyDB.dbo.State s
         INNER JOIN MyDB.dbo.Contract con ON s.StateID = con.ContryID 
                                          AND Opp.OppID = Con.OppID 
         FOR XML PATH('')) AS State, 
        (SELECT State.StateAbbr  + ', ' 
         FROM MyDB.dbo.State s
         INNER JOIN MyDB.dbo.Contract con ON s.StateID = con.ContryID 
                                          AND Opp.OppID = Con.OppID 
         FOR XML PATH('')) AS StateCode, 
        (SELECT Location.LocationName  + ', ' 
         FROM MyDB.dbo.Location l
         INNER JOIN MyDB.dbo.Contract con ON l.LocationID= con.LocationID
                                          AND Opp.OppID = Con.OppID 
         FOR XML PATH('')) AS City
    FROM         ''"
        YourDB.dbo.Opportunity Opp
)
SELECT ...
FROM CTE

P.S.

The "YourDB.dbo.Opportunity" table represent a table with all projects that are available for a contractor

The "MyDB.dbo.Contract" table is a lookup table that joins project with contractors which were hired.

So that is why I need to call FOR XML Function as One Opportunity may have multiple contractors located in different states and cities.

Currently I use the very firs statement to select from the CTE as follows:

SELECT
  ISNULL('| ' + [Contry],'') + ISNULL('| ' + [State],'') + ISNULL('| ' + [City],'')   
 AS ProjectLocation
FROM CTE

But as I said it produced incorrect output.

Answer 1

So you have an entire address in a single table. Here is the setup I expected from you:

declare @Country table (Id int, Name varchar(100));
declare @State table (Id int, Name varchar(100));
declare @Location table (Id int, Name varchar(100));

declare @Opp table (
    Id int,
    CountryId int,
    StateId int,
    LocationId int,
    OpportunityDescription varchar(100)
);

On the schema like that, you need to join all the location tables together, combining their fields into a single output:

select op.*, (
    select concat(c.Name, s.Name, l.Name) as [data()]
    from @Opp p
        inner join @Country c on c.Id = p.CountryId
        inner join @State s on s.Id = p.StateId
        inner join @Location l on l.Id = p.LocationId
    where p.Id = op.Id
    for xml path('')
    ) as [OpportunityLocation]
from @Opp op;