MySql - How to sort vertically stored data to order by one of field's values horizontally?

Question

I have 3 mysql tables named records, fields and fields_values.

Records table consists of meta data about records and record id.

Fields table No. of fields for a record are variable, and can be added dynamically. Fields values contain info about fields shown on the record form eg: what type of fields is it, whether it is required or no etc.

Fields values table This table contains actual data of records for each field.

For example, these tables have data as below:

Records Table:
+----+-------------+------------+--------+
| id | category_id | created_by | status |
+----+-------------+------------+--------+
|  1 |           1 |         10 |      1 |
|  2 |           1 |         10 |      1 |
|  3 |           1 |         10 |      1 |
+----+-------------+------------+--------+

Fields Table:
+----+------------+------------+------+--------+----------+------------+
| id |   title    |   alias    | type | status | required | created_by |
+----+------------+------------+------+--------+----------+------------+
|  1 | First Name | first_name | text |      1 |        1 |        100 |
|  2 | Last Name  | last_name  | text |      1 |        1 |        100 |
|  3 | City       | city       | text |      1 |        1 |        100 |
|  4 | State      | state      | text |      1 |        1 |        100 |
|  5 | Country    | country    | text |      1 |        1 |        100 |
|  6 | Mobile     | mobile     | text |      1 |        1 |        100 |
+----+------------+------------+------+--------+----------+------------+

Fields Values Table:
+----+----------+-----------+------------+
| id | field_id | record_id |   value    |
+----+----------+-----------+------------+
|  1 |        1 |         1 | Andy       |
|  2 |        2 |         1 | A          |
|  3 |        3 |         1 | Manchester |
|  4 |        4 |         1 | NWE        |
|  5 |        5 |         1 | UK         |
|  6 |        6 |         1 | 1234567898 |
|  7 |        1 |         2 | Sandy      |
|  8 |        2 |         2 | B          |
|  9 |        3 |         2 | NYC        |
| 10 |        4 |         2 | NY         |
| 11 |        5 |         2 | USA        |
| 12 |        6 |         2 | 1234567891 |
| 13 |        1 |         3 | Mandy      |
| 14 |        2 |         3 | P          |
| 15 |        3 |         3 | Mumbai     |
| 16 |        4 |         3 | MH         |
| 17 |        5 |         3 | IN         |
| 18 |        6 |         3 | 1234567893 |
+----+----------+-----------+------------+

And, I want to records as below and want to sort it based on one of the fields as selected by user eg: country

+----+------------+-----------+------------+-------+-----------+------------+
| id | first_name | last_name |    city    | state | country ^ |   mobile   |
+----+------------+-----------+------------+-------+---------+------------+
|  3 | Mandy      | P         | Mumbai     | MH    | IN        | 1234567893 |
|  1 | Andy       | A         | Manchester | NWE   | UK        | 1234567898 |
|  2 | Sandy      | B         | NYC        | NY    | USA       | 1234567891 |
+----+------------+-----------+------------+-------+-----------+------------+

How do I sort such vertically stored data to order by one of field's values in a single query so that it can be shown horizontally?

Question is about how to sort such data, it is not about presentation. — Manoj L, Jul 08 '20 at 10:28
This is called a pivot, see e.g. [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/q/12004603). To order, you can e.g. just add `order by 6` (which orders by the 6th column) or `order by country` (if you know its name/alias). — Solarflare, Jul 08 '20 at 10:39
Also, you have multiple identical field_id/record_id tuples. Can this be correct? — Strawberry, Jul 08 '20 at 12:04

score 1 · Accepted Answer · answered Jul 08 '20 at 15:02

Just to observe that it would be more usual to write that this way...

SELECT r.id
     , MAX(CASE WHEN field_id = 1 THEN value END) first_name 
     , MAX(CASE WHEN field_id = 2 THEN value END) last_name
     , MAX(CASE WHEN field_id = 3 THEN value END) city
     , MAX(CASE WHEN field_id = 4 THEN value END) state 
     , MAX(CASE WHEN field_id = 5 THEN value END) country
     , MAX(CASE WHEN field_id = 6 THEN value END) mobile
  FROM records r
  LEFT 
  JOIN fields_values v
    ON v.record_id = r.id
 GROUP
    BY r.id;

Not according to your schema – Strawberry Jul 08 '20 at 16:29 — Strawberry, Jul 08 '20 at 16:29

MySql - How to sort vertically stored data to order by one of field's values horizontally?

1 Answers1