1

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?

  • Do you want a pivot? – dezso May 20 '15 at 9:25
  • The data i get is - ID | num1,num2,num3 I need num1,num2,num3 in separate columns. – user2680821 May 20 '15 at 12:49
  • 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? – SQLHound May 20 '15 at 14:36
  • Adding Columns is what im after. – user2680821 May 20 '15 at 14:42
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

  • Why the MAX()? Seems like you would get a mess. – Rick James Jun 6 '15 at 19:13
  • 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. – SQLHound Jun 8 '15 at 14:36
  • You can have a GROUP BY without any aggregate functions. It's pretty sloppy of the VIEW to be returning duplicate rows. – Rick James Jun 8 '15 at 16:22
  • 1
    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 Jul 16 '15 at 12:09
  • 1
    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. – Andriy M Jul 16 '15 at 12:10
1

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.

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.