I have 3 tables.
+---------+
| items |
+---------+
| item_id |
| ... |
+---------+
+----------------------+
| item_text_values |
+----------------------+
| item_text_value_id |
| item_id |
| custom_field_name_id |
| value |
+----------------------+
+----------------------+
| custom_field_names |
+----------------------+
| custom_field_name_id |
| index |
| name |
+----------------------+
the count of item_text_values is unknown. It can be three 3 or 10 or more. I would like to have a view or stored procedure that returns me row with variable columns depending on how many item_text_values records are there.
I have classic join
SELECT *
FROM items
LEFT JOIN item_text_values ON item_text_values.item_id = items.item_id
LEFT JOIN custom_field_names ON custom_field_names.custom_field_name_id = item_text_values.custom_field_name_id
WHERE items.item_id = 1
which returns me 2 rows if there are 2 records of item_text_values. And I want to merge these two rows into one with variable columns depending on how many item_text_values are there. Don't know if this is even possible.
Thank you