SQL Server usando PIVOT com exemplo de SQL dinâmico
No último artigo, escrevi sobre o uso do PIVOT em um cenário padrão em que você conhece todas as colunas que deseja, usando os mesmos dados que agora podemos construir para produzir nomes de colunas dependentes do conjunto de dados real.
Vou pular a primeira parte desse artigo, mas fornecer o SQL para nos colocar em funcionamento aqui. Primeiro, vamos criar um conjunto de dados.
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row IDMyCol NVARCHAR(10),--Value to become columnCalcCol INT--Value to be Summed)GODECLARE @Piv c1bs_PivINSERT INTO @PivSELECT '1','1',1 UNIONSELECT '1','s',1 UNIONSELECT '1','3',1 UNIONSELECT '2','2',6 UNIONSELECT '2','2',1 UNIONSELECT '2','3',1 UNIONSELECT '3','5',5 UNIONSELECT '3','2',1 UNIONSELECT '3','3',1 UNIONSELECT '3','3',3No outro artigo, codificamos os nomes das colunas (1,2,3), no entanto, isso pode nem sempre ser viável, às vezes os conjuntos de registros podem precisar crescer quando, por exemplo, enviar um conjunto de dados para uma GUI, há um caminho em torno dele, vamos passar por ele passo a passo;
- Crie um tipo de tabela para representar seus dados como acima.
- Declare uma tabela e insira seus dados nela (@Piv)
- Declare duas variáveis, uma para manter os nomes das colunas para Pivot e outra para a lista SELECT, neste exemplo, usando um ISNULL e os nomes das colunas eliminam quaisquer valores NULL. Também usamos COALESCE para concatenar os nomes em uma string.
- Declare outra variável, com a tabela @Piv, são parâmetros que são passados posteriormente.
- Declare nosso SQL, é aqui que injetamos os nomes das colunas e a lista de seleção em uma versão modificada do PIVOT simples.
- EXECUTAR a instrução SQL acima e passar os parâmetros e a tabela @PIV.
--Complex Pivot - unknown column names--Get Column Names for belowDECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) SELECT @Cols=COALESCE(@Cols+',','')+'['+MyCol+']',--Concatenate the Columns @Sel =COALESCE(@Sel +',','')+'ISNULL(['+MyCol+'], 0) AS ['+MyCol+']' --Concatenate the Columns into a select listFROM @Piv GROUP BY MyCol
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [MyRow],'+@Sel+'FROM @PivPIVOT (SUM(CalcCol)FOR MyColIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE sp_executesql @SQL,--SQL String from above@Params,--Parameter list@Piv--Temp Table needs to be passed in, can only be read only?Nosso conjunto de dados agora cresceu para incluir duas colunas adicionais do conjunto de dados (5,s)
| MyRow | 1 | 2 | 3 | 5 | s |
| 1 | 1 | 0 | 1 | 0 | 1 |
| 2 | 0 | 7 | 1 | 0 | 0 |
| 3 | 0 | 1 | 4 | 5 | 0 |
