Vfrdtyky

i have a mysql table and there i have same text but in different language

ID, Lang, Text, number, chapter



 1, English, Hello, 2, 1



 4, German, Hallo, 2, 1



 5, Spanish, Hola, 2, 1

so how can see those like this

Select * from table where chapter=1  (and language: english,german,spanish)



ID, lang1, lang2, lang3, number, chapter



 5, Hello, Hallo, Hola, 2, 1

i want to join columns in 1 row

You can try to use condition aggregate function to make it.

Schema (MySQL v5.7)

CREATE TABLE T(

  ID int,

  Lang varchar(50),

  Text varchar(50),

  number int,

  chapter int

);







INSERT INTO T VALUES (1, 'English', 'Hello', 2, 1);

INSERT INTO T VALUES (4, 'German',  'Hallo', 2, 1);

INSERT INTO T VALUES (5, 'Spanish', 'Hola', 2, 1);

Query #1

SELECT MAX(ID) id,

       MAX(CASE WHEN Lang ='English' THEN Text END) lang1,

       MAX(CASE WHEN Lang ='German' THEN Text END) lang2,

       MAX(CASE WHEN Lang ='Spanish' THEN Text END) lang3,

       number, 

       chapter  

FROM T

GROUP BY number, chapter;



| id  | lang1 | lang2 | lang3 | number | chapter |

| --- | ----- | ----- | ----- | ------ | ------- |

| 5   | Hello | Hallo | Hola  | 2      | 1       |

View on DB Fiddle

Create table test :

 CREATE TABLE test (

                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

                Lang VARCHAR(30) NOT NULL,

                Text VARCHAR(30) NOT NULL,

                number VARCHAR(50),

                chapter INT(10)) 

Insert records:

insert into test values(1,'English','Hello','2',1);

insert into test values(4,'German','Hallo','2',1);     

insert into test values(5,'Spanish','Hola','2',1); 





+----+---------+-------+--------+---------+

| id | Lang    | Text  | number | chapter |

+----+---------+-------+--------+---------+

|  1 | English | Hello | 2      |       1 |

|  4 | German  | Hallo | 2      |       1 |

|  5 | Spanish | Hola  | 2      |       1 |

+----+---------+-------+--------+---------+







SELECT MAX(id) id, MAX(CASE WHEN Lang ='English' THEN Text END) lang1, 

    MAX(CASE WHEN Lang ='German' THEN Text END) lang2, 

    MAX(CASE WHEN Lang ='Spanish' THEN Text END) lang3, number, chapter 

    FROM test GROUP BY number, chapter;



+------+-------+-------+-------+--------+---------+

| id   | lang1 | lang2 | lang3 | number | chapter |

+------+-------+-------+-------+--------+---------+

|    5 | Hello | Hallo | Hola  | 2      |       1 |

+------+-------+-------+-------+--------+---------+

SELECT * FROM

(SELECT LangAS t1, vargu FROM table LANG=114 AND chapter=1) as A,

(SELECT LangAS t2 FROM table WHERE LANG=115 AND chapter=1) AS B,

(SELECT LangAS t3 FROM table  WHERE LANG=116 AND chapter=1) AS C;