user2285831 user2285831 - 1 month ago 6
MySQL Question

How to display lines as dynamic columns with other values?

I want to display orders item that have the collect_id = 2
And I want to display all the fields related to each order_item as columns with values.

These are the tables and the result :

+-------------------------------+
| order_item |
+-------------------------------+
| oi_id oi_price oi_collect_id |
| 1 100 2 |
| 2 30 2 |
| 3 55 3 |
| 4 70 4 |
| 5 220 2 |
| 6 300 4 |
+-------------------------------+

+-----------------------------------+
| field_value |
+-----------------------------------+
| v_value v_fk_field_id oi_fk_id |
| Peter 1 1 |
| Lagaf 2 1 |
| Football 3 1 |
| Male 4 1 |
| 12345678 5 1 |
| Frank 1 2 |
| Loran 2 2 |
| Tennis 3 2 |
| Male 4 2 |
| 11223658 5 2 |
| Nathali 1 5 |
| Waton 2 5 |
| Reading 3 5 |
+-----------------------------------+

oi_fk_id : foreign key ref(order_item.oi_id)
v_fk_field_id : foreign key ref(field.f_id)

+--------------------+
| field |
+--------------------+
| f_id f_label |
| 1 surname |
| 2 name |
| 3 hobbies |
| 4 sex |
| 5 phone |
+--------------------+

+-----------------------------------------------------------------------------+
| Result |
+-----------------------------------------------------------------------------+
| oi_id oi_price oi_collect_id surname name hobbies sex phone |
| 1 100 2 Peter Lagaf Football Male 12345678 |
| 2 30 2 Frank Loran Tennis Male 11223658 |
| 5 220 2 Nathali Waton Reading null null |
+-----------------------------------------------------------------------------+


Important : The table field does not contain only these 5 fields (name, surname, hobbies, sex, phone), but it can contain many others, that the developper may not know, same thing for the correspondant value on the table 'field_value'.

PS : I didn't make field labels as columns in a table because they are dynamic and not limited, and in the front end application, the user can add new fields as he want.

Answer

You can take advantage of dynamic pivoting to get the results:

SELECT      GROUP_CONCAT(t.line)
    FROM    (
        SELECT      CONCAT('MAX(IF(t.l=''', f.f_label, ''',t.v,NULL)) AS ', f.f_label) AS line
            FROM    field f
        ) AS t
    INTO @dynamic;

SELECT      CONCAT('SELECT t.oi_id, t.oi_price, t.oi_collect_id,', 
            @dynamic, 
            ' FROM ( SELECT oi.*, f.f_label AS l, fv.v_value AS v FROM order_item oi JOIN field_value fv ON fv.oi_fk_id = oi.oi_id JOIN field f ON f.f_id = fv.v_fk_field_id WHERE oi.oi_collect_id = 2 ) AS t GROUP BY t.oi_id;')
    INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

However it is limited by GROUP_CONCAT function:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

EDIT - Why MAX function is required?

To solve the given problem, we are creating a dynamic query, based on the content of the field table.

For the given exemplary data, the query without use of MAX function would be:

SELECT      t.oi_id,
            t.oi_price,
            t.oi_collect_id,
            IF(t.l='surname', t.v, NULL) AS surname,
            IF(t.l='name', t.v, NULL) AS name,
            IF(t.l='hobbies', t.v, NULL) AS hobbies,
            IF(t.l='sex', t.v, NULL) AS sex,
            IF(t.l='phone', t.v, NULL) AS phone
    FROM    (
        SELECT      oi.*,
                    f.f_label AS l,
                    fv.v_value as V
            FROM    order_item oi
            JOIN    field_value fv
                ON  fv.oi_fk_id = oi.oi_id
            JOIN    field f
                ON  f.f_id = fv.v_fk_field_id
            WHERE   oi.oi_collect_id = 2
        ) AS t;

Which wouldd result in:

+-------+----------+---------------+---------+-------+----------+------+----------+
| oi_id | oi_price | oi_collect_id | surname | name  | hobbies  | sex  | phone    |
+-------+----------+---------------+---------+-------+----------+------+----------+
|     1 |      100 |             2 | Peter   | NULL  | NULL     | NULL | NULL     |
|     2 |       30 |             2 | Frank   | NULL  | NULL     | NULL | NULL     |
|     5 |      220 |             2 | Nathali | NULL  | NULL     | NULL | NULL     |
|     1 |      100 |             2 | NULL    | Lagaf | NULL     | NULL | NULL     |
|     2 |       30 |             2 | NULL    | Loran | NULL     | NULL | NULL     |
|     5 |      220 |             2 | NULL    | Waton | NULL     | NULL | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | Football | NULL | NULL     |
|     2 |       30 |             2 | NULL    | NULL  | Tennis   | NULL | NULL     |
|     5 |      220 |             2 | NULL    | NULL  | Reading  | NULL | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | NULL     | Male | NULL     |
|     2 |       30 |             2 | NULL    | NULL  | NULL     | Male | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | NULL     | NULL | 12345678 |
|     2 |       30 |             2 | NULL    | NULL  | NULL     | NULL | 11223658 |
+-------+----------+---------------+---------+-------+----------+------+----------+

This is an intermediate result, where each row consists of value for one field and NULL for the others. The MAX function together with a GROUP BY clause is used to combine multiple rows concerning one order item in such a way, that it chooses non null values. It could be replaced by MIN function, which will also favor existing values over null.