How to display one table values as table row and display values for this from another table

Question

I'm trying to combine and display my 2 table are like bellow, My first table structure is,

My second table is,

and my expected result is,

I tried like this but could't get result

select t2.field_value as t2.name
from content_fields t1
Join content_fields_type_richtext_data t2 ON t1.id = t2.field_id;

Is there any mistake in my table structure. How to get this result. Please help any one

Answer 1

MySQL does not have a PIVOT function but you can convert the row data into columns using an aggregate function with a CASE expression:

select t2.object_id,
  max(case when t1.frontend_name = 'Bride Photo' then t2.field_value end) as `Bride Photo`,
  max(case when t1.frontend_name = 'BrideGroom Photo' then t2.field_value end) as `BrideGroom Photo`,
  max(case when t1.frontend_name = 'Bride Description' then t2.field_value end) as `Bride Description`,
  max(case when t1.frontend_name = 'Groom Description' then t2.field_value end) as `Groom Description`,
  max(case when t1.frontend_name = 'Wishes' then t2.field_value end) as `Wishes`
from content_fields t1
inner join content_fields_type_richtext_data t2 
  on t1.id = t2.field_id
group by t2.object_id;

If you have an unknown or dynamic number of values, then you will want to use a prepared statement to get the result:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN t1.frontend_name = ''',
      frontend_name,
      ''' THEN t2.field_value END) AS `',
      frontend_name, '`'
    )
  ) INTO @sql
FROM content_fields;

SET @sql 
  = CONCAT('SELECT t2.object_id, ', @sql, ' 
            from content_fields t1
            inner join content_fields_type_richtext_data t2 
              on t1.id = t2.field_id
            group by t2.object_id');

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