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 — Jorge Campos
– Jorge Campos, Commented Jul 4, 2018 at 12:52

Gaj · Accepted Answer · 2018-07-04 12:47:25Z

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 4, 2018 at 12:47

Gaj

8865 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jpm Over a year ago

Thanks for the quick reply, what if there are there are more than 100 items in product_type, employee_products table.

Gaj Over a year ago

Do you want 100 column + name column?

Rick James · Accepted Answer · 2018-07-05 00:03:46Z

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 5, 2018 at 0:03

Rick James

144k15 gold badges144 silver badges254 bronze badges

Collectives™ on Stack Overflow

Mysql, Dynamic query for rows to column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related