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

Question

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?

Answer 1

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

Answer 2

You essentially want to pivot the meta_value column. Some SQL products have dedicated syntax for this operation. MySQL does not have it, but there is a universal method that works in most products, including MySQL: conditional aggregation.

SELECT
  user_id,
  MAX(CASE meta_key WHEN 'first_name' THEN meta_value END)  AS first_name,
  MAX(CASE meta_key WHEN 'last_name'  THEN meta_value END)  AS last_name,
  MAX(CASE meta_key WHEN 'street_add' THEN meta_value END)  AS street_add,
  MAX(CASE meta_key WHEN 'city'       THEN meta_value END)  AS city,
  MAX(CASE meta_key WHEN 'state'      THEN meta_value END)  AS state
FROM
  t
WHERE
  meta_key IN ('first_name','last_name','street_add','city','state')
GROUP BY
  user_id
;

Fundamentally, this is the same approach as the one suggested by SQLHound, except, in my opinion, there is no need to create a view specifically to solve this problem. (Although it may make sense to create a view for a particular set of pivoted columns if that information is requested often or used in many other queries – makes those queries simpler and more maintainable.) Also, as you can see, pivoting can be done as a single step operation – no need to derive a set of CASEs first and only then aggregate them.