How to combine data in multiple rows in mysql

Question

I'm attempting to combine data in MySQL into a view or stored procedure to either write back out to a new table or access as a view. My database stores customer meta in a separate line with keys and values. I'm puzzled as to how to extract this data in a useful form.

My data is formed this way:

 id order       customer    type        key               value
1   42FF86A1    45858007    shipping    address-name      David Customer
2   42FF86A1    45858007    shipping    email-address     david@email.com
3   42FF86A1    45858007    shipping    number            2125551212
4   42FF86A1    45858007    shipping    address-line1     5353 My Street
5   42FF86A1    45858007    shipping    address-line2     #2
6   42FF86A1    45858007    shipping    city              MyCity
7   42FF86A1    45858007    shipping    region            CA
8   42FF86A1    45858007    shipping    postal-code       95555
9   42FF86A1    45858007    shipping    country           US

Ultimately I'd like to be able to read the data and export it easily for use in my CRM or Excel.

I've attempted to implement https://stackoverflow.com/a/5967720/3001841 but it just doesn't make sense to me how.

Simply getting the data in a single row would be useful to me where:

customer,order,address-name,address-line1,address-line2,city,region,postal-code,country,email-address,number

can you add what you would consider to be 'useful form'? ie an example of how you'd like the data to look? a single row with the key as a column and the value as the corresponding data? — pala_, Apr 15 '15 at 11:53
See: http://stackoverflow.com/questions/9116501/concatenate-multiple-rows-in-single-rows-in-mysql — Viswanath Polaki, Apr 15 '15 at 11:58
Viswanath - I don't beleive that will work due to the "key" and value. that will simply concatenate the data but it's not in neat rows like the example — user3001841, Apr 15 '15 at 12:01

pala_ · Accepted Answer · 2015-04-15T12:46:47.620

Here's an example that uses a view for convenience, but you can bake the view query into the final query as well if you want. (ive used invoice where you've used order, sorry. incidentally, try to avoid using mysql reserved words as field names, its painful having to remember to backtick everything.)

create view order_pivot as (
  select customer,  invoice, type, 
  case when `key` = 'address-name' then `value` end as address,
  case when `key` = 'email-address' then `value` end as email,
  case when `key` = 'number' then `value` end as number,
  case when `key` = 'address-line1' then `value` end as addressline1,
  case when `key` = 'address-line2' then `value` end as addressline2,
  case when `key` = 'city' then `value` end as city,
  case when `key` = 'region' then `value` end as region,
  case when `key` = 'postal-code' then `value` end as postalcode,
  case when `key` = 'country' then `value` end as country
  from orders
);

The view transforms the data into rows with columns that match the number of columns we're interested in. It's not very flexible insofar as it requires you to hardcode those key fields, but it does work. It gives us one value, and a whole bunch of nulls for each row - so obviously we need to coalesce them all into a single row, that's where the next query comes in.

select customer, max(address) addr, max(email) email, max(number) number, max(addressline1) a1, max(addressline2) a2, max(city) city, max(region) region, max(postalcode) postcode, max(country) country
  from order_pivot
  group by customer;

We group by the customer (you would probably want to put a where filter on invoice as well here, and then we can use max() to make sure we ignore all the null values, and grab only the field with valid data.

here's a fiddle

edit

combining the two queries since you lack view permissions it seems:

select customer, max(address) addr, max(email) email, max(number) number, max(addressline1) a1, max(addressline2) a2, max(city) city, max(region) region, max(postalcode) postcode, max(country) country
  from (
  select customer,  invoice, type, 
  case when `key` = 'address-name' then `value` end as address,
  case when `key` = 'email-address' then `value` end as email,
  case when `key` = 'number' then `value` end as number,
  case when `key` = 'address-line1' then `value` end as addressline1,
  case when `key` = 'address-line2' then `value` end as addressline2,
  case when `key` = 'city' then `value` end as city,
  case when `key` = 'region' then `value` end as region,
  case when `key` = 'postal-code' then `value` end as postalcode,
  case when `key` = 'country' then `value` end as country
  from orders
) q
  group by customer;

updated fiddle with combined query

Thank you. I've tried your code but now I'm getting an error: Error Code: 1142. SELECT command denied to user 'myusername'@'myip' for table 'order_pivot' I'm tracking down our db admin to see why it is throwing this error. — user3001841, Apr 15 '15 at 12:44
seems like you lack select privs on views, interesting. i just combined the two queries for you to skip the view - its just a little messier but should still work — pala_, Apr 15 '15 at 12:47

How to combine data in multiple rows in mysql

1 Answers1