SQL PIVOT does not recognize a table

Question

I'm creating a pivot table but this error appears: Message 1087, level 15, state 2, line 6 Declare the table variable "@TABELLA_personale". the @TABELLA_personale has been declared and works, so I don't understand what the problem is.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET
   @columns = N'';
SELECT
   @columns += N', p.' + QUOTENAME((cast(IDUTENTE AS VARCHAR(MAX)) + ' ' + NOME + ' ' + COGNOME + ' ' + (ISNULL (LIVELLO, '')))) 
FROM
   (
      SELECT
         p.IDUTENTE,
         p.NOME,
         p.COGNOME,
         p.LIVELLO 
      FROM
         @TABELLA_personale AS p 
         INNER JOIN
            @TABELLA_completa AS o 
            ON p.IDUTENTE = o.ID 
      GROUP BY
         p.IDUTENTE,
         P.NOME,
         P.COGNOME,
         P.LIVELLO
   )
   AS x;
SET
   @sql = N'

   SELECT
      ' + STUFF(@columns, 1, 2, '') + ' 
   FROM
      (
         SELECT
            p.NOME,
            o.ORELAVORATE 
         FROM
            @TABELLA_personale AS p 
            INNER JOIN
               @TABELLA_completa AS o ONp.IDUTENTE = o.ID 
      )
      AS j PIVOT ( MAX(ORELAVORARE) FOR NOME IN 
      (
         '
 + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

The dynamic sql executes in a different context. You cannot access a table variable in a different context. Use a temporary table instead - `#TABELLA_completa` — Shikhar Arora, Dec 12 '19 at 16:34
Instead of `declare @TABELLA_completa Table` use `Create table #TABELLA_completa` — Shikhar Arora, Dec 12 '19 at 16:38
Also give a read to this so you can understand the differences between a temp table and a table variable - https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server — Shikhar Arora, Dec 12 '19 at 16:45
Does this answer your question? [How to use table variable in a dynamic sql statement?](https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement) — LukStorms, Dec 12 '19 at 19:09

SQL PIVOT does not recognize a table

0 Answers0