Pivot Table / Pivot Table Dynamic SQL Server 2008 R2 .

Post date: Apr 16, 2015 6:20:55 PM

Segue um exemplo de Pivot Table.

No primeiro momento, como é a consulta normal.

/* Query normal */

SELECT [MatrizHabilidades].Id as Id_Matriz , [FTP].Id as Id_Ftp , [FTP_MatrizHabilidades].IdNivelMatrizHabilidades as Nivel

FROM [MatrizHabilidades]

Left outer join [FTP_MatrizHabilidades]

on [FTP_MatrizHabilidades].IdMatrizHabilidades = [MatrizHabilidades].Id

Left Outer Join [NivelMatrizHabilidades]

on [NivelMatrizHabilidades].Id = [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

Left Outer join [FTP]

on [FTP].Id = [FTP_MatrizHabilidades].IdFTP

Where FTP.Ativo = 1

Group By [MatrizHabilidades].Id, FTP.Id, [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

----------------------------------------------------

Cujo resultado é esse da Imagem 1.

Quero agora agrupar o Id_Matriz e transformar o Id_Ftp em colunas, fazendo a relação com o Nivel.

/* Pivot Table */

SELECT *

From

(

SELECT [MatrizHabilidades].Id as Id_Matriz , [FTP].Id as Id_Ftp , [FTP_MatrizHabilidades].IdNivelMatrizHabilidades as Nivel

FROM [MatrizHabilidades]

Left outer join [FTP_MatrizHabilidades]

on [FTP_MatrizHabilidades].IdMatrizHabilidades = [MatrizHabilidades].Id

Left Outer Join [NivelMatrizHabilidades]

on [NivelMatrizHabilidades].Id = [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

Left Outer join [FTP]

on [FTP].Id = [FTP_MatrizHabilidades].IdFTP

Where FTP.Ativo = 1

Group By [MatrizHabilidades].Id, FTP.Id, [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

) teste

Pivot

(

MAX(Nivel) FOR Id_Ftp IN ([1], [2], [3], [4], [5])

) PivotTable;

--Aqui o Id_FTP vira a coluna e o Nível o valor da coluna

---------------------------------------------------------------

Como o código fica, na imagem 2.

Agora preciso que isso venha de forma dinamica, não fixo como está de 1 até 5.

Então precisamos usar Dynamic SQL

/* Pivot Dinamico */

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(FTP.Id)

from FTP

group by FTP.Id

order by FTP.Id

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

--Select @cols --Seleciona as colunas

set @query = 'SELECT *

From

(

SELECT [MatrizHabilidades].Id as Id_Matriz , [FTP].Id as Id_Ftp , [FTP_MatrizHabilidades].IdNivelMatrizHabilidades as Nivel

FROM [MatrizHabilidades]

Left outer join [FTP_MatrizHabilidades]

on [FTP_MatrizHabilidades].IdMatrizHabilidades = [MatrizHabilidades].Id

Left Outer Join [NivelMatrizHabilidades]

on [NivelMatrizHabilidades].Id = [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

Left Outer join [FTP]

on [FTP].Id = [FTP_MatrizHabilidades].IdFTP

Where FTP.Ativo = 1

Group By [MatrizHabilidades].Id, FTP.Id, [FTP_MatrizHabilidades].IdNivelMatrizHabilidades

) teste

Pivot

(

MAX(Nivel) FOR Id_Ftp IN (' + @cols + ')

) PivotTable '

execute(@query);

----------------------------------------------------------------------------------------

Como fica, na figura 3