Combine tables with values other tables with column names in SQL

Question

I have 5 tables. One with individual details, two with column names for the latter two and two with values.

T1: people
-----------------
|name|age|gender|
-----------------
|Alice|22 | f   |
|Bob  |33 | m   |
-----------------

T2: skillgroup names
--------------
|id | name    |
---------------
| 1 | sgn1    |
| 2 | sgn2    |
|.. |  ...    |
| 63| sgn63   |
---------------

T3: skill names
--------------
|id | name    |
---------------
|101 | sk101  |
|102 | sk102  |
|..  |  ...   |
|180 | sk180  |
---------------

T4: skillgroup person
--------------------------------
|name  | skillgroupID | value  |
--------------------------------
|Alice | 1            | 40     |
|Alice | 2            | 38     |
|...   | ..           | ..     |
|Bob   | 63           | 25     |
--------------------------------

T5: skill person
---------------------------
|name  | skillID | value  |
---------------------------
|Alice | 101     | 80     |
|Alice | 102     | 70     |
|...   | ..      | ..     |
|Bob   | 180     | 90     |
---------------------------

However, I would like to get a table like this:

    ------------------------------------------------------------
    |name |age|gender|sgn1|sgn2|...|sgn63|sk101|sk102|...|sk180|
    ------------------------------------------------------------
    |Alice| 22| f    | 40 | 38 |...| ... | 80  |  70 |...| ... |
    |Bob  | 33| m    | ...| ...|...|  25 | ... | ... |...| 90  |
    ------------------------------------------------------------

How can I do this? As far as I can see, the skillgroup and skills are the same for each person, meaning that each person has the same amount of skillgroups and skills.

score 0 · Accepted Answer · answered Feb 01 '18 at 10:48

The following mysql query solves your particular problem:

SELECT
  p.name,
  p.age,
  p.gender,
  MAX(IF(skgn.name = 'sgn1', skgp.value, NULL)) AS sgn1,
  MAX(IF(skgn.name = 'sgn2', skgp.value, NULL)) AS sgn2,
  MAX(IF(skgn.name = 'sgn63', skgp.value, NULL)) AS sgn63,
  MAX(IF(skn.name = 'sk101', skp.value, NULL)) AS sk101,
  MAX(IF(skn.name = 'sk102', skp.value, NULL)) AS sk102,
  MAX(IF(skn.name = 'sk180', skp.value, NULL)) AS sk180
FROM people AS p
INNER JOIN skillgroup_person AS skgp ON p.name = skgp.name
INNER JOIN skill_person AS skp ON p.name = skp.name
INNER JOIN skillgroup_names AS skgn ON skgp.skillgroupID = skgn.id
INNER JOIN skill_names AS skn ON skp.skillID = skn.id
GROUP BY p.name, p.age, p.gender;

See DEMO

Since this solution is not dynamic, in case you have lot of columns to turn into row data it is better to go with a dynamic pivot table solution at this SO answer.

Combine tables with values other tables with column names in SQL

1 Answers1