As long as the dynamic solution is based on the static one, for reasons of clarity I'll first explain the static one by assuming that, as in the example your provided, there are exactly 3 fields at max, for addition and question fields.
Let's look at the static solution first, by assuming that we have specifically 3 fields. In this case what you can do is compute a row number for each addition and question, which will be used to match the specific value at the required index for each of the three fields addition1, addition2 and addition3 (same goes for question), using an IF statement. In order to remove the NULL values that are generated by this statement, we can select the maximum value and aggregate over item_id, name and surname
WITH cte AS(
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY name, surname
ORDER BY IF(addition IS NULL, 1, 0),
ID ) AS rn_add,
ROW_NUMBER() OVER(
PARTITION BY name, surname
ORDER BY IF(question IS NULL, 1, 0),
ID ) AS rn_qst
FROM tab
)
SELECT item_id AS ID,
item_id,
name,
surname,
MAX(IF(rn_add = 1, addition, NULL)) AS addition1,
MAX(IF(rn_add = 2, addition, NULL)) AS addition2,
MAX(IF(rn_add = 3, addition, NULL)) AS addition3,
MAX(IF(rn_qst = 1, question, NULL)) AS question1,
MAX(IF(rn_qst = 2, question, NULL)) AS question2,
MAX(IF(rn_qst = 3, question, NULL)) AS question3,
SUM(amount) AS amount
FROM cte
GROUP BY item_id,
name,
surname
Check the demo here.
The dynamic solution aims at reproducing that exact same query as a prepared statement (which is essentially a string that you first build and then ask MySQL to execute over the database), with the only difference that it needs to generalize on the amount of fields to extract:
MAX(IF(rn_add = 1, addition, NULL)) AS addition1,
MAX(IF(rn_qst = 1, addition, NULL)) AS question1,
...
...
MAX(IF(rn_add = <n>, addition, NULL)) AS addition<n>,
MAX(IF(rn_qst = <n>, addition, NULL)) AS question<n>,
And we need to reproduce these instructions n times with n equals to the item_id's highest amount of both addition and question values. In order to generate this piece of query, we get the longest list of indices:
WITH cte AS(
SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY item_id) AS idx
FROM tab
)
and cycle over it to generate all MAX rows as a string where, in place of the specific number (as in the static query), we will use all the numbers stored inside cte.idx:
SELECT GROUP_CONCAT(
CONCAT('MAX(IF(rn_add = ', cte.idx, ', addition, NULL)) AS addition', cte.idx, ','
'MAX(IF(rn_qst = ', cte.idx, ', question, NULL)) AS question', cte.idx
)) INTO @sql
FROM cte;
Once we have the generalized amonut of MAX rows, we can just use this together with the rest of the static query, which does not depend on the number of addition or question values.
SET @cte = 'WITH cte AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(addition IS NULL, 1, 0), ID) AS rn_add, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(question IS NULL, 1, 0), ID) AS rn_qst FROM tab)';
SET @sql = CONCAT(@cte,
'SELECT item_id AS ID, item_id, name, surname,',
@sql,
',SUM(amount) AS amount FROM cte GROUP BY item_id, name, surname'
);
Once we have the static query generated as a string in a dynamic way, we can ask MySQL to prepare, execute and deallocate it.
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
The execution will show you your desired output.
Here's the full code for the dynamic query:
SET @sql = NULL;
WITH cte AS(
SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY item_id) AS idx
FROM tab
)
SELECT GROUP_CONCAT(
CONCAT('MAX(IF(rn_add = ', cte.idx, ', addition, NULL)) AS addition', cte.idx, ','
'MAX(IF(rn_qst = ', cte.idx, ', question, NULL)) AS question', cte.idx
)) INTO @sql
FROM cte;
SET @cte = 'WITH cte AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(addition IS NULL, 1, 0), ID) AS rn_add, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(question IS NULL, 1, 0), ID) AS rn_qst FROM tab)';
SET @sql = CONCAT(@cte,
'SELECT item_id AS ID, item_id, name, surname,',
@sql,
',SUM(amount) AS amount FROM cte GROUP BY item_id, name, surname'
);
SELECT @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
Check the demo here.
Side Note: if you want to store the output of this query, you may require to create a view inside the prepared statement. In that case you should change the @sql assignment to:
SET @sql = CONCAT('CREATE VIEW my_view AS ',
@cte,
'SELECT item_id AS ID, item_id, name, surname,',
@sql,
',SUM(amount) AS amount FROM cte GROUP BY item_id, name, surname'
);
hence select the content of the view whenever you need it, for example to export it to Excel.
