How do I transpose and simplify my SQL table with a query?

Question

EDIT: Adding the original tables I did a full join on

Exercise Table:

+---------+-----------+------------+------------+
| Column1 |  Column2  |  Column3   |  Column4   |
+---------+-----------+------------+------------+
| 1_e_id  | Chest     | Strength   | Situps     |
| 2_e_id  | Arms      | Strength   | Pushups    |
| 3_e_id  | Full Body | Cardio     | Running    |
| 4_e_id  | Full Body | Stretching | Gynmastics |
| 5_e_id  | Full Body | Cardio     | Swimming   |
| 6_e_id  | Legs      | Strength   | Leg Pulls  |
| 7_e_id  | Full Body | Resting    | Rest       |
| 8_e_id  | Legs      | Strength   | Lunges     |
| 9_e_id  | Arms      | Stretching | Stretching |
| 10_e_id | Legs      | Strength   | Raises     |
+---------+-----------+------------+------------+

Workout Table:

+-----------+---------+---------+--------------------+
|  Column1  | Column2 | Column3 |      Column4       |
+-----------+---------+---------+--------------------+
| 1_w_e_id  | 1_w_id  | 1_e_id  | Strength Workout   |
| 2_w_e_id  | 1_w_id  | 2_e_id  | Strength Workout   |
| 3_w_e_id  | 1_w_id  | 4_e_id  | Strength Workout   |
| 4_w_e_id  | 2_w_id  | 3_e_id  | Stretching Workout |
| 5_w_e_id  | 2_w_id  | 6_e_id  | Stretching Workout |
| 6_w_e_id  | 2_w_id  | 7_e_id  | Stretching Workout |
| 7_w_e_id  | 2_w_id  | 9_e_id  | Stretching Workout |
| 8_w_e_id  | 3_w_id  | 2_e_id  | Glutes Workout     |
| 9_w_e_id  | 3_w_id  | 5_e_id  | Glutes Workout     |
| 10_w_e_id | 4_w_id  | 6_e_id  | Energy Workout     |
| 11_w_e_id | 4_w_id  | 8_e_id  | Energy Workout     |
| 12_w_e_id | 4_w_id  | 10_e_id | Energy Workout     |
+-----------+---------+---------+--------------------+

Full join: WHERE Exercise.Column1=Workout.Column3

SQL queries make my head spin and I can't figure this one out. I've used a full join and select to get it down to the following:

+---------+------------+----------------------+----------------------+
| Column1 |  Column4   |   Workouts.Column2   |   Workouts.Column4   |
+---------+------------+----------------------+----------------------+
| 4_e_id  | Gymnastics | 1_w_id               | Strength Workout     |
| 1_e_id  | Situps     | 1_w_id               | Strength Workout     |
| 2_e_id  | Pushups    | 1_w_id               | Strength Workout     |
| 6_e_id  | Leg Pulls  | 2_w_id               | Stretching Workout   |
| 9_e_id  | Stretching | 2_w_id               | Stretching Workout   |
| 7_e_id  | Rest       | 2_w_id               | Stretching Workout   |
| 3_e_id  | Running    | 2_w_id               | Stretching Workout   |
| 5_e_id  | Swimming   | 3_w_id               | Glutes Workout       |
| 2_e_id  | Pushups    | 3_w_id               | Glutes Workout       |
| 10_e_id | Raises     | 4_w_id               | Energy Workout       |
| 8_e_id  | Lunges     | 4_w_id               | Energy Workout       |
| 6_e_id  | Leg Pulls  | 4_w_id               | Energy Workout       |
+---------+------------+----------------------+----------------------+

where #_e_id is the exercise_id (Column4) and #_w_id is the workout_id (Workouts.Column4).

And I'd like to get it to the following where the exercises are organized by workout.

+--------------------+------------+---------+------------+---------+
|      Column1       |  Column2   | Column3 |  Column4   | Column5 |
+--------------------+------------+---------+------------+---------+
| Strength Workout   | Gymnastics | Pushups | Situps     |         |
| Stretching Workout | Leg Pulls  | Rest    | Stretching | Running |
| Glutes Workout     | Swimming   | Pushups |            |         |
| Energy Workout     | Raises     | Lunges  | Leg Pulls  |         |
+--------------------+------------+---------+------------+---------+

How can I do this via a SQL query or rather, subquery at this point?

I'm lost. Your original data seems to have duplicate column names. — Gordon Linoff, Jul 22 '20 at 14:59
I've used a full join can't have doesn;t exist in mysql. Please add your 2 original tables — P.Salmon, Jul 22 '20 at 15:04

score 0 · Answer 1 · answered Jul 22 '20 at 15:06

0

if instead of having the result in separate columns, you can just get the values in the same row you can use group_concat ..

    select  Workouts.Column4, group_conca(your_table.Column4)
    from your_table 
    group by  Workouts.Column4

answered Jul 22 '20 at 15:06

ScaisEdge

131,976
10
91
107

This might work.... I'm not familiar with GROUP_CONCAT() but it looks like I can specify the concatenate myself which could accomplish close to having them in separate columns. My ultimate goal is then to use this table to fill out a table via JS and HTML. – usafutb0l3r Jul 22 '20 at 15:21
group_concat return just a string with comma separated value .. if you need separated column group_concat is not the right way .. anyway once you have the comma separated values in string you could manage these result server side for format the proper html table – ScaisEdge Jul 22 '20 at 15:25
See the code here: http://mysql.rjweb.org/doc.php/pivot – Rick James Jul 23 '20 at 00:11
Just wanted to give an update @scaisEdge, that worked! Thank you so much and apologies again for the confusing question. – usafutb0l3r Jul 23 '20 at 02:37
@usafutb0l3r well if my answer is right please (past 15 minutes) mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – ScaisEdge Jul 23 '20 at 05:52

How do I transpose and simplify my SQL table with a query?

1 Answers1