CODEWITHSUNDEEP
sqlsql-server
user2171512
user2171512

Reputation: 561

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'

Upvotes: 0

Views: 916

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This sounds like string_agg():

SELECT g.[Id], STRING_AGG(br.name, ',')
FROM [Game] g JOIN
     [GameAvailability] ga
     ON ga.[GameId] = g.[Id] JOIN
     [Portal] por
     ON por.Id = ga.PortalId JOIN
     [Brand] br
     ON br.Id = por.BrandId
GROUP BY g.Id;

Upvotes: 1

Related Questions