concatenate the same column in consultation?

Navigation
1

First of all I am sorry for knowing how to use all the available tools .. I will try to teach them as I can ..

I have this query: 

   Nombre    Clase    Raza      Contexto
   Lazaro    Burlón   Humano    Lazaro es un ...
   Lazaro    Barbaro  Humano    Lazaro es un ...
   Quintin   Paladin  Orco      QUintin es un ...
   tiruriru  Bardo    Elfo      Tiruriru es un ...

Ok then I have the problem that I charge a datagridview with the query that gives me that back .. and doubles me, triples, quadruples (depends on the number of classes) some characters. I have tried a thousand ways, but I am learning and I feel very frustrated .. someone can help me to stay in such a way that there are no duplicated characters .. that is: 

  Nombre     Clase                       Raza      Contexto
  Lazaro     Burlón, Barbaro             Humano    Lazaro es un ...
  Quintin    Paladin                     Orco      QUintin es un ...
  tiruriru   Bardo                       Elfo      Tiruriru es un ...
  tinkiwinki Bardo,Guerrero, caballero   Gnomo     Tinkiwinki es un ...

I add the query but I do not know if it is: S: 

string command = string.Format(select p.nombre Nombre, c.nombre Clase, p.nivel Nivel, r.nombre Raza, p.descripcion_historia Contexto, 
p.descripcion_personalidad Personalidad from jugador j inner join 
jugador_personaje jp on j.id_jugador = jp.id_jugador inner join personaje p 
on p.id_personaje = jp.id_personaje inner join personaje_clases pc on 
p.id_personaje = pc.id_personaje inner join clases c on pc.id_clase = 
c.id_clase inner join raza r on p.id_raza = r.id_raza where j.nombre = 
'{0}'", this.usuario);

DDL scheme 

CREATE TABLE Personajes
    ('id_personaje' int(11), 'Nombre' varchar(20));

INSERT INTO Personajes
    ('id_personaje','Nombre')
VALUES
    (1,'Lazaro'),
    (2,'Quintin'),
    (3,'Tiruriru');

CREATE TABLE Clases
('id_clase' int(11), 'Nombre' varchar(20));

INSERT INTO Clases
    ('id_clase','Nombre')
VALUES
    (1,'Burlón'),
    (2,'Caballero'),
    (3,'Barbaro'),
    (4,'Paladin'),
    (5,'Bardo');
CREATE TABLE clases_personajes
(id_personaje int(11), id_clase int(11));

INSERT INTO clases_personajes
    (id_personaje, id_clase)
VALUES
  (1,1),(1,3),(2,4), (3,2),(3,5);
    
asked by Yosi 02.07.2018 в 22:06
source

1 answer

1

To join these columns you have two options, one is to obtain the results and then process them in the code, simpler and cleaner, although you would get more data: 

var groupedResults = list.GroupBy(jugador => jugador.Nombre)
    .ToDictionary(keyValue => keyValue.Key, keyValue => keyValue.ToList());
foreach (var keyValuePair in groupedResults)
{
    var classesAsString =
        string.Join(", ", keyValuePair.Value.Select(jugador => jugador.Clase));
    Console.WriteLine(
        $"El jugador {keyValuePair.Key} tiene las clases {classesAsString}");
}

With this you group the results in a diction where you have everyone, you can go through the dictionary to get the data

Another way is to use the FOR XML PATH ('') of SqlServer along with a Group By , so at the end of your query you can put the Group By by name and concatenate the classes in a column of your choice 

SELECT DISTINCT emp_name,
STUFF(
(SELECT ', ' + RTRIM(proj_id)
FROM project_members AS t1 
WHERE t1.emp_name = t2.emp_name
FOR XML PATH (''))
, 1, 1, '')
FROM project_members t2

If you have questions, check the documentation of FOR XML PATH

If you are not using SQL Server, you can see this answer for other DBs

    
answered by 02.07.2018 в 23:10
Problem with eventFilter and Key_Return in a QDialog with buttons template twig header and footer always visible without using {% block [...]%}