Error while creating a view with procedure MySQL

Question

I have a problem when I try to create a view using a procedure. I have to do that because I need to make a pivot in MySQL, converting rows of a table in columns of another. The query works great, but when I put it in the "CREATE VIEW" statement it gives me error.

Here is the query with CREATE view

CREATE VIEW `Untitled` AS 
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(formazioni_persone.id_formazione = ',
      formazioni.id,
      ', true, false)) AS "',
      formazioni.titolo,'"'
    )
  ) INTO @sql
FROM formazioni;

SET @sql = CONCAT('SELECT persone.*, ', @sql, ' FROM persone INNER JOIN formazioni_persone ON persone.id = formazioni_persone.id_persona GROUP BY persone.id');

PREPARE stmt1 FROM @sql; 

EXECUTE stmt1; 

DEALLOCATE PREPARE stmt1;

The query without CREATE VIEW Untitled AS works great

The query without CREATE VIEW Untitled AS works great. I already tried to create a TEMP TABLE inside the CREATE VIEW, but nothing. Also tried to use delimiters like that, but nothing

DELIMITER $$ 
CREATE VIEW `Untitled` AS 
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(formazioni_persone.id_formazione = ',
      formazioni.id,
      ', true, false)) AS "',
      formazioni.titolo,'"'
    )
  ) INTO @sql
FROM formazioni;

SET @sql = CONCAT('SELECT persone.*, ', @sql, ' FROM persone INNER JOIN formazioni_persone ON persone.id = formazioni_persone.id_persona GROUP BY persone.id');

PREPARE stmt1 FROM @sql; 

EXECUTE stmt1; 

DEALLOCATE PREPARE stmt1;
END $$ 
DELIMITER ;

Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @sql = NULL' at line 2, Time: 0.082000s

With all due respect, when you make up syntax out of thin air, the chances that it will work are small. — Bill Karwin, Jul 24 '19 at 19:13
Yes, that's true. But in MySQL there is no way to make pivot. The only way I found is using IF / CASE. And to make it dynamically (because the table from which I get columns will change) I think I haven't any other solutions... But any suggestion is appreciated — Akylle, Jul 24 '19 at 19:19

Bill Karwin · Accepted Answer · 2019-07-25T18:44:46.953

A VIEW is not a procedure. A VIEW is only a single SELECT statement, which must have fixed columns at the time you define the VIEW. You can't make a VIEW that is also a procedure.

Sorry, if you need a pivot-table, you need to specify the values for each column in the query. You can't make a SELECT query or a VIEW that dynamically adds more columns as it finds potential future values.

And you can't define a VIEW that runs an arbitrary block of procedure code anyway. That would require a procedure.

You should just use the solutions that are already shown in questions like MySQL - Rows to Columns

There are no other shortcuts or workarounds.

By the way, all SQL databases have this restriction, not just MySQL.

Re your question:

I'm looking for a solution that doesn't require manual update of the query

A pivot-table query must have as many columns in the select-list as the number of columns you want it to return. There is no way to make an SQL query that expands the number of columns dynamically as a result of the data it reads at execute time.

The only way you can make a single query that returns all the data is to NOT do a pivot-table query, and instead return all the data in rows, not columns.

SELECT p.*, f.titolo, pf.id_persona IS NOT NULL AS ha_formazioni
FROM persone AS p 
CROSS JOIN formazioni AS f
LEFT OUTER JOIN formazioni_persone AS fp ON fp.id_formazioni AND fp.id_persona = p.id

This will return one row for each formazioni per persone. Then you must write code in your application to loop over all the rows of the reesult, and format the data in columns in the manner you want.

Thank you, now that's clear. Have you got any suggestion to make that dynamic? The number of CASE/IF in the query will change, and I'm looking for a solution that doesn't require manual update of the query — Akylle, Jul 25 '19 at 08:27

Error while creating a view with procedure MySQL

1 Answers1