Home > Scripts > PIVOT dinamico in SQL Server 2005

PIVOT dinamico in SQL Server 2005

La funzione PIVOT di SQL Server 2005 non permette di utilizzare una variabile per la definizione delle colonne da creare come risultato dell’operazione di pivoting.

E’ possibile superare questo limite utilizzando del codice SQL dinamico, creando al volo la query da eseguire ed mandandola in esecuzione con il comando sp_executesql.

use tempdb
go

set nocount on
go

/*

 Setup scenario di esempio

*/

if (object_id(‘dbo.tabColonne’) is not null) drop table dbo.[tabColonne];
if (object_id(‘dbo.tab1’) is not null) drop table dbo.[tab1];
go

CREATE TABLE [dbo].[tabColonne](
    [idMacroSettoreEconomico] [int] NOT NULL,
    [denominazione] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL
)
go

INSERT INTO [dbo].[tabColonne] VALUES(1, ‘tipo1’)
INSERT INTO [dbo].[tabColonne] VALUES(2, ‘tipo2’)
INSERT INTO [dbo].[tabColonne] VALUES(3, ‘tipo3’)
go

CREATE TABLE [dbo].[tab1](
    [idTab1] [int] NOT NULL,
    [idMacroSettoreEconomico] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
    [anno] [int] NOT NULL,
    [addetti] [int] NOT NULL,
    [nome] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL
 
)
go

INSERT INTO [dbo].[tab1] VALUES(1,1,2006,10,’marco’)
INSERT INTO [dbo].[tab1] VALUES(2,2,2005,30,’marco’)
INSERT INTO [dbo].[tab1] VALUES(3,3,2004,20,’marco’)

INSERT INTO [dbo].[tab1] VALUES(4,1,2006,10,’luca’)
INSERT INTO [dbo].[tab1] VALUES(5,2,2005,30,’luca’)
INSERT INTO [dbo].[tab1] VALUES(6,3,2004,20,’luca’)

INSERT INTO [dbo].[tab1] VALUES(7,1,2006,10,’rosi’)
INSERT INTO [dbo].[tab1] VALUES(8,2,2005,30,’rosi’)
INSERT INTO [dbo].[tab1] VALUES(9,3,2004,20,’rosi’)
go

/*

 Implementazione Pivot dinamica

*/

— Crea una stringa di valori separati da virgola
— che contiene i nomi dei valori da pivotare (ossia da trasformare in colonne)
declare @s varchar(max);
set @s = ”;
with cte as (
 select distinct [denominazione] from dbo.tabColonne s
)
select @s = @s + [denominazione] + ‘,’ from cte;
set @s = substring(@s, 1, len(@s)-1);

— Esegue la query con un pivoting dinamico
— Esegue la query con un pivoting dinamico
declare @q nvarchar(max);
set @q = N’
 with    cte
          as ( select                         
                        t.[addetti],
                        t.[nome],
                        tc.[denominazione]                from     [dbo].[tab1] as t
                        inner join [dbo].[tabColonne] as tc on t.[idMacroSettoreEconomico] = tc.[idMacroSettoreEconomico]              )
    select 
  *
    from
  cte pivot ( sum(addetti) for denominazione in ( ‘ + @s + ‘ ) ) as pvt’

exec sp_executesql @q;

 

Nel caso fosse necessario manipolare ulteriormente il risultato dell’esecuzione del suddetto codice, è necessario utilizzare una tabella temporanea di appoggio dove salvare l’output della stored procedure, cosi da poter avere una tabella fisica da utilizzare per successive elaborazioni. E’ possibile far ciò utilizzando il comando INSERT nella forma INSERT FROM … EXEC:

create table #t2
(
 nome varchar(100),
 tipo1 int,
 tipo2 int,
 tipo3 int
)
insert into #t2 exec sp_executesql @q
go

select * from #t2
go

drop table #t2
go

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …