Select distinct values from table and put them in string

I have the following tables:

SELECT
    g.[Id],
    --br.name
    --por.id
  FROM [Game] AS g
  INNER JOIN [GameAvailability] AS ga ON ga.[GameId] = g.[Id]
  INNER JOIN [Portal] AS por ON por.Id = ga.PortalId
  INNER JOIN [Brand] AS br ON br.Id = por.BrandId

What I want to accomplish is to put all available brand names from [Brand] table connected to some Game in one string and select those values in the select clause.

Each gameid can be connected to more than one portal and brand.

For example, my final result would be as shown below:

GameId        BrandNameList
1             'test1, test2'
2             'test3,test 4'

Answer

You can try the following query.

SELECT DISTINCT g.Id,
  STUFF((SELECT distinct ',' + g1.[name]
         FROM 
         (
           SELECT g.[Id],
           br.name,
           FROM [Game] AS g
           INNER JOIN [GameAvailability] AS ga ON ga.[GameId] = g.[Id]
           INNER JOIN [Portal] AS por ON por.Id = ga.PortalId
           INNER JOIN [Brand] AS br ON br.Id = por.BrandId
         )
         g1
         WHERE g.Id = g1.Id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') BrandNameList
FROM 
(
           SELECT g.[Id],
           br.name,
           FROM [Game] AS g
           INNER JOIN [GameAvailability] AS ga ON ga.[GameId] = g.[Id]
           INNER JOIN [Portal] AS por ON por.Id = ga.PortalId
           INNER JOIN [Brand] AS br ON br.Id = por.BrandId
)g;