1

Originally asked at Query based on column values in row.

I have the sqlfiddle at http://sqlfiddle.com/#!9/362f1cd/2.

I have a customer table containing columns like id, name, mobile, email and a weekly summary table which has columns like id, customerid, day_of_week, type_of_food, name_of_food. The type_of_food column has values like breakfast, lunch, dinner, protein, snack, etc (can be n kind).

What i need is a report like

Customer Name, Mobile, Email, Breakfast, Lunch, Breakfast2, Snack1, Snack2, ... (can be upto n)

Acc1, Mob1, Email1, name_of_breakfast_food, name_of_lunch_food, name_of_dinner_food, ... Acc2, Mob2, Email2, name_of_breakfast_food, name_of_lunch_food, name_of_dinner_food, ...

Please keep in mind that there can be multiple type of food in the same day, like 2 breakfasts and 3 snacks for one account.

Please note that the question @ MySQL pivot row into dynamic number of columns only helps in getting the count of elements (type_of_food), while I need them separate (and the values from name_of_lunch_food) in case there are 2 entries (even of the same type).

Edit: Adding expected result based on the sqlfiddle

Name, Mobile, Email, Day, Breakfast, Snack, Lunch, Snack1, Dinner, Snack2

acc1, 1234, acc1@yopmail.com, Mon, Eggs, Fries, Rice, Apple, Bread, Curd

acc1, 1234, acc1@yopmail.com, Tue, Bread, - , - , - , Milk, -

acc2, 2345, acc2@yopmail.com, Mon, Eggs, Fries, Rice, Apple, Bread, -

acc2, 2345, acc2@yopmail.com, Tue, - , Curd, Bread, - , Milk, -

pinaki
  • 5,393
  • 2
  • 24
  • 32

1 Answers1

1

I would use group_concat function:

SELECT c.id, c.name, c.email, w.day_of_week,
    group_concat(CASE WHEN w.type_of_food = 'Breakfast' THEN w.name_of_food END ) As breakfests,
    group_concat(CASE WHEN w.type_of_food = 'Snack' THEN w.name_of_food END ) As Snacks,
    group_concat(CASE WHEN w.type_of_food = 'Lunch' THEN w.name_of_food END ) As Lunches,
    group_concat(CASE WHEN w.type_of_food = 'Dinner' THEN w.name_of_food END ) As Dinners
FROM customer c
JOIN weekly_report w ON c.id = w.customerid
GROUP BY c.id, c.name, c.email, w.day_of_week

Demo: http://sqlfiddle.com/#!9/362f1cd/3

| id | name |            email | day_of_week | breakfests |           Snacks | Lunches | Dinners |
|----|------|------------------|-------------|------------|------------------|---------|---------|
|  1 | acc1 | acc1@yopmail.com |         Mon |       Eggs | Fries,Apple,Curd |    Rice |   Bread |
|  1 | acc1 | acc1@yopmail.com |         Tue |      Bread |           (null) |  (null) |    Milk |
|  2 | acc2 | acc2@yopmail.com |         Mon |       Eggs |      Fries,Apple |    Rice |   Bread |
|  2 | acc2 | acc2@yopmail.com |         Tue |     (null) |             Curd |   Bread |    Milk |
krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • This is brilliant. Just curious, there is no way to get the multiple snacks in different columns from sql right? – pinaki Dec 26 '18 at 07:27