I want to transpose the rows into columns and the number of rows is dynamic.
I have checked some of the questions, they are using
CASE
where meeting_id=1
meeting_id, first_name, last_name, occupation
1, danyal , sandeelo , engineer
You can use GROUP_CONCAT and Prepared statements to handle dynamic number of fields in MySQL Pivot.
Please find query below with some test data.
Prepare Data
CREATE TABLE Meeting
(
ID INT,
Meeting_id INT,
field_key VARCHAR(100),
field_value VARCHAR(100)
);
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'danyal');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'sandeelo');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Matthew');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (7,2,'field4' , 'xyz');
SQL Query
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when field_key = ''',
field_key,
''' then field_value end) ',
field_key
)
) INTO @sql
FROM
Meeting;
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, '
FROM Meeting
GROUP BY Meeting_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;