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'
Advertisement
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;