mySQL: Return results from one column as multiple columns

Question

First time asking a question on here, so apologizes if this ends up being formatted poorly.

I'm trying to pull some information from multiple tables in order to build a report. Said tables are coming from a Magento instance, in case that helps any.

So, I have four tables I'm working with: customer_entity, customer_address_entity, customer_address_entity_text, and customer_address_var_char

In customer_entity, I need to get three fields: entity_id, email, and group_id

In customer_address_entity, I need to get parent_id (which equals entity_id from above) and entity_id (which is not the same as above).

In customer_address_entity_text, I need entity_id, attribute_id, and value.

In customer_address_entity_varchar, I need value, entity_id, and attribute_id.

My issue mainly lies with the last table. I need to get value for six attribute ids, but I want to have value as multiple columns (first name, last name, email, etc) rather than returning as just value.

Here's my current code:

SELECT
customer_entity.entity_id as "Customer ID", email, customer_address_entity_text.value as "Street Address", customer_address_entity_varchar.value
FROM
customer_entity, customer_address_entity, customer_address_entity_text, customer_address_entity_varchar
WHERE
customer_entity.group_id="2"
AND
customer_entity.entity_id = customer_address_entity.parent_id
AND
customer_address_entity.entity_id = customer_address_entity_text.entity_id
AND
customer_address_entity_text.attribute_id="24"
AND
customer_address_entity.entity_id = customer_address_entity_varchar.entity_id
AND
customer_address_entity_varchar.attribute_id in (19,21,25,26,27)

This returns results formatted like so:

Current

What I'd like is:

Desired

Yes, the column order isn't the greatest, but I'll deal with that after I get columns formatted as desired. I've tried a couple substring queries (one of which eventually crashed the server; oops) and using EXISTS. Yes, my code is hideous too; I plan on cleaning that up after I get the data I'm looking for in the format I'm looking for.

Thanks!

Edit: Should anyone want a copy of the code I ended up deploying (hat tip to Bernd):

SELECT
e.entity_id as "Customer ID", e.email, t.value AS "Street Address",
GROUP_CONCAT(IF(v.attribute_id = 19,v.value,NULL)) AS "First Name",
GROUP_CONCAT(IF(v.attribute_id = 21,v.value,NULL)) AS "Last Name",
GROUP_CONCAT(IF(v.attribute_id = 25,v.value,NULL)) AS "City",
GROUP_CONCAT(IF(v.attribute_id = 27,v.value,NULL)) AS "Region/State",
GROUP_CONCAT(IF(v.attribute_id = 26,v.value,NULL)) AS "Country"
FROM customer_entity e 
LEFT JOIN customer_address_entity a ON a.parent_id = e.entity_id
LEFT JOIN customer_address_entity_varchar v ON v.entity_id = a.entity_id
LEFT JOIN customer_address_entity_text t on t.entity_id = a.entity_id
WHERE e.group_id = 2
AND t.attribute_id = 24
GROUP BY v.entity_id

could you provide tables structure? and samples of data for each table? — Alex
– Alex, Commented Oct 7, 2015 at 17:59
9kb. Dumped to OneDrive There's only one example data set included, but it matches what a production example would look like. — Stephen Weber
– Stephen Weber, Commented Oct 7, 2015 at 19:32

Bernd Buffen · Accepted Answer · 2015-10-07 19:11:09Z

It is not necessary to JOIN the table more then one time. You can group the result and get the Values. Here a Sample

Two Tables

MariaDB [tmp]> select *from names;
+----+-------+----------------+
| id | name  | email          |
+----+-------+----------------+
|  1 | Bernd | [email protected] |
|  2 | David | [email protected] |
+----+-------+----------------+
2 rows in set (0.00 sec)

MariaDB [tmp]> select * from customer_address_entity;
+-----------+--------------+------------------+
| entity_id | attribute_id | value            |
+-----------+--------------+------------------+
|         1 |            1 | Duesseldorf      |
|         1 |            2 | 40211            |
|         1 |            3 | berlinerplatz 55 |
|         1 |            4 | 0211 / 1234567   |
|         2 |            1 | Bremen           |
|         2 |            2 | 21334            |
|         2 |            3 | Aachenerstr. 99  |
|         2 |            4 | 0432 / 7890111   |
+-----------+--------------+------------------+
8 rows in set (0.00 sec)

Join the Table

MariaDB [tmp]> SELECT
    ->   n.*,a.*
    -> FROM `names` n
    -> LEFT JOIN customer_address_entity a ON a.entity_id = n.id;
+----+-------+----------------+-----------+--------------+------------------+
| id | name  | email          | entity_id | attribute_id | value            |
+----+-------+----------------+-----------+--------------+------------------+
|  1 | Bernd | [email protected] |         1 |            1 | Duesseldorf      |
|  1 | Bernd | [email protected] |         1 |            2 | 40211            |
|  1 | Bernd | [email protected] |         1 |            3 | berlinerplatz 55 |
|  1 | Bernd | [email protected] |         1 |            4 | 0211 / 1234567   |
|  2 | David | [email protected] |         2 |            1 | Bremen           |
|  2 | David | [email protected] |         2 |            2 | 21334            |
|  2 | David | [email protected] |         2 |            3 | Aachenerstr. 99  |
|  2 | David | [email protected] |         2 |            4 | 0432 / 7890111   |
+----+-------+----------------+-----------+--------------+------------------+
8 rows in set (0.00 sec)

GROUP it and get the fields

MariaDB [tmp]> SELECT
    ->   n.name, n.email,
    ->   GROUP_CONCAT( IF ( a.attribute_id = 1 , a.value,NULL)) AS city,
    ->   GROUP_CONCAT( IF ( a.attribute_id = 2 , a.value,NULL)) AS plz,
    ->   GROUP_CONCAT( IF ( a.attribute_id = 3 , a.value,NULL)) AS street,
    ->   GROUP_CONCAT( IF ( a.attribute_id = 4 , a.value,NULL)) AS phone
    -> FROM `names` n
    -> LEFT JOIN customer_address_entity a ON a.entity_id = n.id
    -> GROUP BY a.entity_id;
+-------+----------------+-------------+-------+------------------+----------------+
| name  | email          | city        | plz   | street           | phone          |
+-------+----------------+-------------+-------+------------------+----------------+
| Bernd | [email protected] | Duesseldorf | 40211 | berlinerplatz 55 | 0211 / 1234567 |
| David | [email protected] | Bremen      | 21334 | Aachenerstr. 99  | 0432 / 7890111 |
+-------+----------------+-------------+-------+------------------+----------------+
2 rows in set (0.00 sec)

MariaDB [tmp]>

Suleymanov Rashid · Accepted Answer · 2015-10-07 18:23:44Z

0

MySQL supports join table itself, so you need to join eav table multiple times and extract 'value' field: (example)

   SELECT customer_entity.entity_id as "Customer ID"
   customer_address_street.value as "Street" 
   LEFT JOIN customer_address_entity_varchar as customer_address_street
   ON customer_address_street.entity_id = customer_entity.entity_id
   WHERE customer_address_street.attribute_id = 19;

answered Oct 7, 2015 at 18:23

Suleymanov Rashid

1232 silver badges8 bronze badges

Collectives™ on Stack Overflow

mySQL: Return results from one column as multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related