UGISS User Group Italiano SQL Server
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
