Join into pivot (?)-CodePudding

I wonder how to replace such a table (the table is the result of 3x LEFT JOIN)

ID	item_id	name	surname	addition	question	amount
1	1	Gladys	Warner	hot-dog	mayo	14
2	1	Gladys	Warner	pizza	chilli	11
3	2	Harrison	Croft	pizza
4	2	Harrison	Croft	burger	chilli	11
5	2	Harrison	Croft	hod-dog	mayo	14

to somthing like

ID	item_id	name	surname	addition	addition2	addition3	question1	question2	question3	amount
1	1	Gladys	Warner	hot-dog	pizza	-	mayo	chilli	-	25
2	2	Harrison	Croft	pizza	burger	hod-dog	chilli	mayo	-	25

the number of additions or questions may increase or decrease, depending on person.

CodePudding user response：

Will just throw this here as a possibility - it won't give you dynamic columns but may be of use depending on how you intend to consume the data.

It's certaintly less faff and more performant if you can.

select 
    item_Id, name, surname, 
    group_concat(addition separator ', ') Additions,
    group_concat(question separator ', ') Questions,
    Sum(amount) amount
from t
group by item_Id, name, surname;

CodePudding user response：

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.