mysqli: merge rows in 1 based on some parameters

Question

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

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Nov 14 '18 at 11:05
Consider handling data display related requirements in your application code (eg: PHP, C++. Java etc) — Madhur Bhaiya, Nov 14 '18 at 11:05
If you don't mind to get one `lang` column with a comma separted string you can also consider GROUP_CONCAT (https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat) instead.. Besides it's more eazy because the pivot query needs to be created dynamic to support more columns. — Raymond Nijland, Nov 14 '18 at 11:12

score 2 · Answer 1 · edited Nov 14 '18 at 12:57

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 |
+------+-------+-------+-------+--------+---------+

score 1 · Accepted Answer · answered Nov 14 '18 at 11:54

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

score 1 · Answer 3 · answered Nov 14 '18 at 12:05

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;

answered Nov 14 '18 at 12:05

arlind

165
1
3
15

mysqli: merge rows in 1 based on some parameters

3 Answers3