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](https://stackoverflow.com/questions/12004603/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](https://stackoverflow.com/questions/44062168/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