Mysql – Get multiple column and row values in a sigle row

join;MySQLpivot

I have a table structure as:

KEY     language_id        language_value  
------------------------------------------
SELECT      1               Select Lang 1  
SELECT      2               Select lang 2
GET         2               get lang 2

I want a result like 

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   Select Lang 1         Select lang 2
GET                            get lang 2

If Search for a key "GET"

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   get lang 1

Thanks

Best Answer

This process is called pivot. This is mostly done with GROUP BY and using MAX or SUM.

Create table/insert data. 

CREATE TABLE t
    (`KEY` VARCHAR(6), `language_id` INT, `language_value` VARCHAR(13))
;

INSERT INTO t
    (`KEY`, `language_id`, `language_value`)
VALUES
    ('SELECT', 1, 'Select Lang 1'),
    ('SELECT', 2, 'Select lang 2'),
    ('GET', 2, 'get lang 2')
;

Query

SELECT
   t.key
 , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
 , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
FROM 
 t
GROUP BY
 t.key
ORDER BY 
 t.key ASC

Result

key     language_val_1  language_val_2  
------  --------------  ----------------
GET     (NULL)          get lang 2      
SELECT  Select Lang 1   Select lang 2

Searching for KEY "GET".

You need to put the pivot query as a delivered table so you can filter with a WHERE statement.

Query

SELECT 
    t_pivot.key
  , t_pivot.language_val_1
  , t_pivot.language_val_2
FROM (

  SELECT
     t.key
   , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
   , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
  FROM 
   t
  GROUP BY
   t.key
  ORDER BY 
   t.key ASC 
) 
 AS
   t_pivot
WHERE
 t_pivot.key = 'GET'

Result

I got a different result as your example. But it looks like your example result is wrong. 

key     language_val_1  language_val_2  
------  --------------  ----------------
GET     (NULL)          get lang 2
Related Question