Search code examples
mysqlgroup-concat

MySQL GROUP_CONCAT and flip two rows in one

I have table

id | industry_id | type | key
1        1           0    word1
2        1           1    word2
3        1           0    word3
4        1           1    word4
5        2           0    word5
6        2           1    hello
7        2           0    world

In result i need to get table

industry_id | title       | description
1           | word1 word3 | word2 word4
2           | word5 world | hello

It means that all words with type 0 go to title, with type 1 to description

For now i can get almost what i want with query (but still get 2 records for each industry)

SELECT industry_id, type, GROUP_CONCAT(`key` SEPARATOR ' ') AS TEXT FROM table GROUP BY industry_id, type
Solution

  • You are grouping by industry_id, type so you will get 4 rows and two row for each industry id try this 

    SELECT industry_id, 
GROUP_CONCAT(CASE WHEN `type`=0 THEN `key` END SEPARATOR ' ') AS title,
GROUP_CONCAT(CASE WHEN `type`=1 THEN `key` END SEPARATOR ' ') AS description
FROM Table1 
GROUP BY industry_id

    See Fiddle