Mysql, Dynamic query for rows to column

Question

I've three tables with name employee, employee_products, product_type

reference : http://sqlfiddle.com/#!9/00436/4

I'm trying to get data as Let this table is Table_1

using this query:

select emp.name, 
(select count(*) from employee_products where product_type_id = 1 and employee_id = emp.id) as Service,
(select count(*) from employee_products where product_type_id = 2 and employee_id = emp.id) as Product,
(select count(*) from employee_products where product_type_id = 3 and employee_id = emp.id) as Other
from employee as emp;

but, i think it is not efficient and from every new product_type_id i've to alter this query, can I do this dynamically.

+------------+---------+---------+-------+--+
| Name       | Service | Product | Other |  |
+------------+---------+---------+-------+--+
| Bezos      | 1       | 0       | 0     |  |
+------------+---------+---------+-------+--+
| Steve      | 0       | 3       | 0     |  |
+------------+---------+---------+-------+--+
| Bill gates | 1       | 0       | 0     |  |
+------------+---------+---------+-------+--+
| Tim Cook   | 0       | 0       | 1     |  |
+------------+---------+---------+-------+--+

and Let this table is Table_2

In this I can't figure out how this is even possible in mysql as there is no pivot feature in mysql.

+------------+---------+---------+---------+---------+-----------+-------+
| Name       | Amazon  | iPhone  | iPad    | iPod    | Microsoft | IDK   |
+------------+---------+---------+---------+---------+-----------+-------+
| Bezos      | Service | NULL    | NULL    | NULL    | NULL      | NULL  |
+------------+---------+---------+---------+---------+-----------+-------+
| Steve      | NULL    | Product | Product | Product | NULL      | NULL  |
+------------+---------+---------+---------+---------+-----------+-------+
| Bill gates | NULL    | NULL    | NULL    | NULL    | PRODUCT   | NULL  |
+------------+---------+---------+---------+---------+-----------+-------+
| Tim Cook   | NULL    | NULL    | NULL    | NULL    | NULL      | OTHER |
+------------+---------+---------+---------+---------+-----------+-------+

Please help.

Note : There can be more than 100 items in product_type, employee_products table.

Possible duplicate of [Mysql query to dynamically convert rows to columns on the basis of two columns](https://stackoverflow.com/questions/17964078/mysql-query-to-dynamically-convert-rows-to-columns-on-the-basis-of-two-columns) — Jorge Campos, Jul 04 '18 at 12:52

score 0 · Answer 1 · answered Jul 04 '18 at 12:47

0

Try this for Table_1

Select name, Max(Service) as Service, Max(Product) as Product, Max(Other) as Other
From (
    select e.name, 
            count(case when ep.product_type_id = 1 then 1 else null end) as Service,
            count(case when ep.product_type_id = 2 then 1 else null end) as Product,
            count(case when ep.product_type_id = 3 then 1 else null end) as Other,
    from employee e 
            inner join employee_products ep on (e.id = ep.employee_id)
)
Group by name;

Note: same way you can try for Table_2

answered Jul 04 '18 at 12:47

Gaj

888
5
5

Thanks for the quick reply, what if there are there are more than 100 items in product_type, employee_products table. – jpm Jul 04 '18 at 12:51
Do you want 100 column + name column? – Gaj Jul 04 '18 at 12:53

score 0 · Answer 2 · answered Jul 05 '18 at 00:03

0

There are several bits of code out there for dynamically building and running the SELECT with the columns dynamically deduced. Here is mine.

answered Jul 05 '18 at 00:03

Rick James

135,179
13
127
222

Mysql, Dynamic query for rows to column

2 Answers2