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?

• András Váczi: Do you want a pivot?
• user2680821: The data i get is - ID | num1,num2,num3 I need num1,num2,num3 in separate columns.
• SQLHound: Just to be clear, are you looking to alter the table by adding columns, or are you just trying to split the results of the above select query?
• user2680821: Adding Columns is what im after.

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

• Rick James: Why the MAX()? Seems like you would get a mess.
• SQLHound: The MAX() is due to the grouping and the necessity of an aggregate. As long as the ids are unique, it shouldn't cause a "mess" in this particular case.
• Rick James: You can have a GROUP BY without any aggregate functions. It's pretty sloppy of the VIEW to be returning duplicate rows.
• Andriy M: It was a disservice on @RickJames's part to talk you out of using MAX(). The fact that you can reference non-GROUP BY columns without aggregation in MySQL doesn't mean you should. MAX() is important in this particular case. Otherwise your second view will always be returning just nulls in all columns (other than user_id) except one or sometimes without an exception.
• Andriy M: And, by the way, you don't really need two views for this, because you can do CASE and MAX in one go: max(case when Item_Type = "first_name" then meta_value end) as first_name.

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.