Query with multiple line results in only one line

Question

I am developing a school project in asp.net and I was trying to get a query, that results in several lines (which is corret), to show the results for each "result" on only one row, the thing is that the project is a web page to sell games. Each of the games has several developper, Genres(Action, FPS etc.), formats (digital or physical), platforms (ps3, ps4 etc.) and other setting like these. I tested it and It returns several rows, as I said before, that could be synthesised into a single row with several values in which the columns repeat.

Here is the query:

SELECT 
    dbo.Jogos.NomeJogo AS Jogo, 
    dbo.Plataforma.Plataforma,
    dbo.Jogos.disponibilidade, 
    dbo.Jogos.Preco AS Preço, 
    dbo.Jogos.Stock, 
    dbo.Desenvolvedora.NomeDesenvolvedora AS Desenvolvedora, 
    dbo.PEGI.PEGI, 
    dbo.Formato.Formato, 
    dbo.Genero.Genero, 
    dbo.Fornecedor.NomeFornecedor AS Fornecedor 
FROM dbo.Jogos 
    INNER JOIN dbo.Desenvolvedora ON dbo.Jogos.IdDesenvolvedora = dbo.Desenvolvedora.IdDesenvolvedora 
    INNER JOIN dbo.PEGI ON dbo.Jogos.IdPegi = dbo.PEGI.IdPEGI 
    INNER JOIN dbo.GeneroJogo ON dbo.Jogos.IdJogo = dbo.GeneroJogo.IdJogo 
    INNER JOIN dbo.Genero ON dbo.GeneroJogo.IdGenero = dbo.Genero.IdGenero 
    INNER JOIN dbo.JogosFormato ON dbo.Jogos.IdJogo = dbo.JogosFormato.IdJogo 
    INNER JOIN dbo.Formato ON dbo.JogosFormato.IdFormato = dbo.Formato.IdFormato 
    INNER JOIN dbo.JogosFornecedor ON dbo.Jogos.IdJogo = dbo.JogosFornecedor.IdJogo 
    INNER JOIN dbo.Fornecedor ON dbo.JogosFornecedor.IdFornecedor = dbo.Fornecedor.IdFornecedor 
    INNER JOIN dbo.JogosPlataforma ON dbo.Jogos.IdJogo = dbo.JogosPlataforma.IdJogo 
    INNER JOIN dbo.Plataforma ON dbo.JogosPlataforma.IdPlataforma = dbo.Plataforma.IdPlataforma

The query returns several lines for the same game which could be resumed to only one.

For example:

Game              |  Genre   |  Platform  |  Developper
___________________________________________________________

Assassin's Creed  |  Action  |  Ps3       |  Ubisoft
Assassin's Creed  |  Stealth |  Ps3       |  Ubisoft
Assassin's Creed  |  Action  |  xBox 360  |  Ubisoft

I'd like to obtain something like:

Game              |  Genre           |  Platform       |  Developper
_____________________________________________________________________

Assassin's Creed  |  Action, Stealth |  Ps3, Xbox 360  |  Ubisoft

I'm asking for assistance because I don't see how this could be possible.

Any help would be appreciated.

P.S.: A few values on the query are in Portuguese. I've also checked this thread How to use GROUP BY to concatenate strings in SQL Server? But this one uses only one table and I'm fetching data from several tables so I really don't know how to proceed in this one.

Thank you in advance Best regards, Kevin

Possible duplicate of this one http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Lanorkin, Sep 09 '15 at 17:14
Have you considered using an ORM like Entity Framework? What you're talking about would make a pretty easy LINQ query, but it's kind of hacky to do with straight SQL, which like to think of everything in nice flat tables. — StriplingWarrior, Sep 09 '15 at 17:14

A_Sk · Answer 1 · 2015-09-09T20:24:11.253

This is Called group_concat in sql server

Like:

CREATE TABLE #GAME ([NAME] varchar(50), [Genre] VARCHAR(50), [Platform] varchar(50),Developper VARCHAR(50))

INSERT INTO #GAME  VALUES ('A','g1','ps1','User1'),('A','g2','ps2','User2'),('A','g3','ps1','User1')


SELECT 
  [NAME],
  STUFF((SELECT ', ' + [Genre] FROM #GAME WHERE (NAME = Results.NAME) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS GEN1,
  STUFF((SELECT ', ' + [Platform] FROM #GAME WHERE (NAME = Results.NAME) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS Platform1,
  STUFF((SELECT ', ' + Developper FROM #GAME WHERE (NAME = Results.NAME) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS Developper1

FROM #GAME Results
GROUP BY NAME

EDIT 2:

CREATE TABLE #GAME ([NAME] varchar(50), [Genre] VARCHAR(50), [Platform] int,Developper VARCHAR(50))
INSERT INTO #GAME  VALUES ('A','g1',1,'User1'),('A','g2',2,'User2'),('A','g3',3,'User1')

Create Table #PLATFORM(ID int,NAME varchar(50),)
INSERT INTO #PLATFORM  VALUES (1,'ps1'),(2,'ps2'),(3,'ps3')


;with cte as
(select gm.Name as Name,pl.Name as plt
from #GAME gm join #PLATFORM pl on gm.Platform=pl.ID
)

select Name,
STUFF((SELECT ', ' + plt FROM cte WHERE (NAME = Results.NAME) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS Platform1
FROM cte Results
GROUP BY NAME

I get the idea of how this one works out, but my question is how can I apply this concept to get the data related to the game but are in different tables? Because for example I have the games table and the platforms table and it's a N-N relation. Does it still work this way? I'm a bit confused |: My teacher unfortunately didn't teach us way more than select, update, insert, delete :S — Kevin Ferreira, Sep 09 '15 at 19:05

score 0 · Accepted Answer · answered Sep 09 '15 at 17:45

There are several ways to go about this. The accepted answer to the question that Lanorkin referenced in his/her comment shows one of them that works with SQL Serer (XML PATH). If this sounds esoteric or scary in any way, you can use a correlated subquery with a user-defined function that concatenates the values for you.

The basic idea is that you have an outer query that returns each of the unique values for the games and an inner query that pulls the concatenated lists for genre, platform, or developer.

create function getPlatforms (@jogoId nvarchar(50))
returns nvarchar(1024)
as 
begin
  declare @platforms nvarchar(1024);
  select @platforms = '';
  -- I understand that your Platform is normalized in your schema, but 
  -- you get the ide. Just make this a join. You can also strip out the 
  -- leading comma one you are done.
  select @platforms = @platforms + ', ' + Plataforma from dbo.Jogos 
        where JogoId = @jogoid;
  return @platforms;
end;
go;


SELECT 
    dbo.Jogos.NomeJogo AS Jogo, 
    dbo.getPlatforms(JogoId)
FROM dbo.Jogos 
GROUP BY NomeJogo, JogoId;

Unfortunately, group_concat is not available (out of the box) in SQL Server, i.e. concatenation is not one of the aggregation operations (unlike SUM, COUNT, AVG etc which are).

A comparison of the various methods, including how they perform, is here.

Thank you for your reply, I understood the concept easily and how the function you wrote might work, the thing is I'm really new to using functions and triggers and stuff like that since our teacher only taught us the four basic comands, I didn't really understood how I would add a Join there to actually make it work. I understood though that I need to basically have a function for each "value" I'd like to concatenate, right? — Kevin Ferreira, Sep 09 '15 at 19:17
yes correct, if you follow this route. I like this way because it is "procedural" and easy to understand for a novice, but again, it is not the best performing option. — cdonner, Sep 10 '15 at 17:18

score 0 · Answer 3 · answered Sep 09 '15 at 20:09

As others have stated you can use xml path to group concat. However from personal experience working with large data sets, I found it's much faster to get the results from your query and then concatenate them in the code.

For example you can create an object as so:

public class Game
{
    public string Title { get; set; }
    public string Genre { get; set; }
    public string Platform { get; set; }
    public string Developer { get; set; }
}

Then you can use linq to manipulate the data set returned from the query as you see fit. For example:

using(var command = new SqlCommand())
{
     /*init your sql command */
     var dataSet = new DataSet("Games");
     var dataAdapter = new SqlDataAdapter();
     /*set data adapter command (your query)*/
     dataAdapter.Fill(dataSet);

     var games = new List<Game>();

     var groupings = dataSet.Tables[0].Cast<DataRow>().GroupBy(x => x["Game"].ToString()); //Here we are grouping by games. Based on your example we'll have one group "Assassin's Creed". 

     foreach(var grouping in groupings)
     {
          var firstRow = grouping.First();

          var game = new Game()
          {
               Title = firstRow["Game"].ToString(),
               Genre = string.Join(",", grouping.GroupBy(x => x["Genre"]).Select(x => x.First()["Genre"].ToString()), //Grouping eliminates dupes. This should return Action, Stealth based on your example
               Platform = string.Join(",", grouping.GroupBy(x => x["Platform"]).Select(x => x.First()["Platform"].TosString()),
               Developer = firstRow["Developer"].ToString() //I'm assuming each game has exactly one developer
          }
          games.Add(game);
    }
    //The games list should now have exactly what you need. Based on your example it would have only looped once since there is only one game so you'll have a list with one game object
}

I'm not sure if this helps you but I've noticed this is a lot faster then doing group by xml path in sql server.

Query with multiple line results in only one line

3 Answers3

Linked