MySql View based on three tables and transpose

Question

How can I create a view in mysql?

Attaching the screenshot as required.

you should better post your table schema as a text but not the image — Alex, Jun 08 '16 at 13:00
Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Alex, Jun 08 '16 at 13:01

sagi · Accepted Answer · 2016-06-08T14:16:11.993

0

You can solve it with conditional aggregation (If I understood correctly table1 contains the ID, and table2 contain the products?

Use this:

CREATE VIEW YourViewName AS
SELECT t.id,
       MAX(CASE WHEN t.product = 'apple' then 'Y' END) as apple,
       MAX(CASE WHEN t.product = 'guava' then 'Y' END) as guava,
       MAX(CASE WHEN t.product = 'mango' then 'Y' END) as mango,
       MAX(CASE WHEN t.product = 'cherry' then 'Y' END) as cherry,
       MAX(CASE WHEN t.product = 'carrot' then 'Y' END) as carrot
FROM(SELECT t1.id,t3.product FROM Table1 t1
     LEFT JOIN Table3 t3
      ON(t1.id = t3.id)) t
GROUP BY t.id

edited Jun 08 '16 at 14:16

answered Jun 08 '16 at 13:04

sagi

40,026
6
59
84

there are 3 tables.... Table 1 has one column called id, Table 2 has one column called type. these these 2 tables are not connected. table 3 has 2 columns.. id and type – Deepak Kumar Jun 08 '16 at 13:06
result is not showing correct... All the id's in table 1 are not coming in the result.. – Deepak Kumar Jun 08 '16 at 13:33
Ah, my bad, now:) @DeepakKumar – sagi Jun 08 '16 at 13:34
now all the columns apple, guava, mango etc. are showing Y as their column value.. @sagi – Deepak Kumar Jun 08 '16 at 14:08
Perfect... Cheers.. :) – Deepak Kumar Jun 08 '16 at 14:26

MySql View based on three tables and transpose

1 Answers1