MySQL - Pivot row values into column groups based on value of another column

Question

I am trying to build a product comparison page. I have the following MySQL table. Think of item_id column as product_id

+---------+------------------+-------------------------+
| item_id | field_name       | field_value             |
+---------+------------------+-------------------------+
|       1 | Operating System | Windows 7               |
|       1 | Processor        | Core i3                 |
|       1 | Brand            | HP                      |
|       2 | Operating System | Windows 10              |
|       2 | Processor        | Core i7                 |
|       2 | Brand            | Dell                    |
+---------+------------------+-------------------------+

I am trying to get a result where i can compare values for Brand, Operating System and Processor for item1 (item_id=1) and item2 (item_id=2) side by side as a single row. The number of products to be compared (i.e:- columns) are a minimum of two and a maximum of four.

I have seen the answer found here -> MySQL pivot row into dynamic number of columns but the difference is my dynamic columns are based on the the value of item_id while the field_name values will be used on one column. The values of field_value will be added to the dynamic columns based on item_id. Based on the answer, I tried the following case based statement but it created some unnecessary null values

SELECT 
    field_name,
    CASE
        WHEN price_fid = 1 THEN field_value
    END AS item1,
    CASE
        WHEN price_fid = 2 THEN field_value
    END AS item2
FROM
    custom_fields;

Result:

+------------------+-----------+-------------------------+
| field_name       | item1     | item2                   |
+------------------+-----------+-------------------------+
| Operating System | Windows 7 | NULL                    |
| Processor        | Core i3   | NULL                    |
| Brand            | HP        | NULL                    |
| Operating System | NULL      | Windows 10              |
| Processor        | NULL      | Core i7                 |
| Brand            | NULL      | Dell                    |
+------------------+-----------+-------------------------+

As you can see the Brand, Operating System and processor are found twice in the field_name column and there are null values for both item1 and item2. I also took a look at the following answers -> Pivot values on column based on grouped columns in SQL but there still is a problem with either null values or repeated field_names.

What i am trying to get is the value for both items (products) on a single row. Here is the result i am looking for.

+------------------+-----------+------------+
| field_name       | item1     | item2      |
+------------------+-----------+------------+
| Brand            | HP        | Dell       |
| Operating System | Windows 7 | Windows 10 |
| Processor        | Core i3   | Core i7    |
+------------------+-----------+------------+

Is it possible to run a MySQL query to get output like the above? If so, is there a way to easily add a third and fourth item_id (i.e:- columns) to it. I am looking for a case based or other type of query which i can implement easily call from PHP. If it's not possible via a standard mysql query, i still wouldn't mind a t-sql statement or procedure. Thanks

Why not handle matters of presentation in application code? – Jul 29 '21 at 22:21 — , Jul 29 '21 at 22:21

score 2 · Accepted Answer · answered Jul 29 '21 at 22:13

A much more dynamic approach is.

You can add A WHERE item_id IN (1,2,3,4) to the first sql Statement, and schoose the item_ids you want to see

CREATE TABLE custom_fields (
  `item_id` INTEGER,
  `field_name` VARCHAR(16),
  `field_value` VARCHAR(10)
);

INSERT INTO custom_fields
  (`item_id`, `field_name`, `field_value`)
VALUES
  ('1', 'Operating System', 'Windows 7'),
  ('1', 'Processor', 'Core i3'),
  ('1', 'Brand', 'HP'),
  ('2', 'Operating System', 'Windows 10'),
  ('2', 'Processor', 'Core i7'),
  ('2', 'Brand', 'Dell');

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when item_id = ''',
      item_id,
      ''' then field_value ELSE NULL END) AS item',
      item_id
    )
  ) INTO @sql
from custom_fields;

SET @sql = CONCAT('SELECT field_name, ', @sql, ' FROM custom_fields
group by field_name');

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


field_name       | item1     | item2     
:--------------- | :-------- | :---------
Operating System | Windows 7 | Windows 10
Processor        | Core i3   | Core i7   
Brand            | HP        | Dell

db<>fiddle here

One thing i noticed is that if you have a new row which has item_id = 3, field_name = 'Display' and field_value='6.5 inch', when using your suggested query, you will still get a row where field_name = Display and the dynamic column values equal to null. I added where item_id in (1, 2) on the last sql statement. Is there a way to avoid those rows whose item_id are not equal to 1 or 2. — user3659497, Jul 30 '21 at 06:17
It looks perfect to me https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f80aa835ad21087b985ce8d64a51308 as otem1 and 2 don't have any Display, it can't have a value in a table, that is how table work, so show me what reulst you expect and i will see, but i think youshould let that do the aplication but if you see site like dell or so and they compare Displays .with Mouse, that they have similar output, but they also have a order row where they set an orer of displaying things. But in Reality you will always compare aples with apples and so the basic categories should be equal with low emptys — nbk, Jul 30 '21 at 09:02

score 1 · Answer 2 · answered Jul 29 '21 at 21:46

1

You can group by field_name and use conditional aggregation:

SELECT field_name,
       MAX(CASE WHEN item_id = 1 THEN field_value END) item1,
       MAX(CASE WHEN item_id = 2 THEN field_value END) item2,
       MAX(CASE WHEN item_id = 3 THEN field_value END) item3 -- for a 3d column
       .....................................................
FROM custom_fields
GROUP BY field_name

See the demo.

answered Jul 29 '21 at 21:46

forpas

160,666
10
38
76

Works exactly as i wanted it to. I actually like your answer since it much more shorter and can be easily implement from php. – user3659497 Jul 30 '21 at 05:24
One thing i noticed is that if you have a new row which has item_id = 3, field_name = 'Display' and field_value='6.5 inch', when using your query, you will still get a row where field_name = Display and the dynamic column values equal to null. Is there a way to avoid those rows from the results whose item_id are not equal to 1 or 2. – user3659497 Jul 30 '21 at 06:26
@user3659497 the pattern of the sample data you posted implies that if you have field_name = 'Display' it would be in all item_ids, just like Brand which exists in all item_ids and in this case the code works fine: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b4c4a2de7f9938e003f62d2cecf91d75 – forpas Jul 30 '21 at 06:38

MySQL - Pivot row values into column groups based on value of another column

2 Answers2