Mysql – Select Multiple Values from Same Column; one sql statement and split into separate columns

MySQLpivot

This post derives from following –
Select Multiple Values from Same Column; one sql statment

Based on this query – 

 SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) 
 FROM t 
 WHERE meta_key  IN('first_name','last_name','street_add','city','state') 
 GROUP BY user_id

It will give me a comma separated column, which is great and gives me the data, but is there a way to separate the fields into columns?

Best Answer

The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example from the referenced post, you may actually want:

create view yourview1 as (
  select
    user_id,
    case when Item_Type = "first_name" then meta_value end as first_name,
    case when Item_Type = "last_name" then meta_value end as last_name,
    case when Item_Type = "street_add" then meta_value end as street_add,
    case when Item_Type = "city" then meta_value end as city,
    case when Item_Type = "state" then meta_value end as state
  from User_Items
); 

create view yourview1_Pivot as (
  select
    user_id,
    MAX(first_name) as first_name,
    MAX(last_name) as last_name,
    MAX(street_add) as street_add,
    MAX(city) as city,
    MAX(state) as state 
  from yourview1
  group by user_id
);

source

Related Question