0

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

0

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      

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.