SQL Server usando UNPIVOT para transformar colunas em linhas
SQL Server usando a função UNPIVOT em um cenário de tipo do mundo real.
O caso para se livrar de CASE
Muitos dos projetos em que trabalhamos envolviam o trabalho com dados armazenados em uma tabela com os meses de 1 a 12 armazenados por ano. Esta não é uma solução incomum, mas pode fazer com que as instruções SQL fiquem inchadas com dezenas de instruções CASE.
Essas instruções CASE tornam a manutenção mais difícil e também podem ser uma fonte de erro humano.
Abaixo, criamos um conjunto de dados simples definido de acordo com um exemplo do mundo real, e o SQL Server tem uma função que transformará colunas em linhas.
UnPivID | UnPivYear | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
1 | 2010 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
1 | 2011 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
1 | 2012 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2 | 2010 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
O caso para se livrar de CASE
Se você estiver executando abaixo do SQL Server 2012, você precisará criar esta função, que é equivalente à função DATEFROMPARTS construída em 2012
O caso para se livrar de CASE
Em seguida, podemos criar uma tabela temporária e preenchê-la com os dados de exemplo acima.
SQL
DECLARE @UnPiv TABLE (UnPivID INT, UnPivYear SMALLINT, M1 INT, M2 INT, M3 INT, M4 INT, M5 INT, M6 INT, M7 INT, M8 INT, M9 INT, M10 INT, M11 INT, M12 INT)INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)SELECT 1,2010,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)SELECT 1,2011,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)SELECT 1,2012,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)SELECT 2,2010,1,2,3,4,5,6,7,8,9,10,11,12
SELECT UnPivID,UnPivYear,REPLACE(col,'M','') UnPivMonth,dbo.c1bs_DateFromParts(UnPivYear,REPLACE(col,'M',''),1) UnPivDate,DateFromParts(UnPivYear,REPLACE(col,'M',''),1) UnPivDateFrom2012,valFROM @UnPivUNPIVOT (Val FOR col IN (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)) unpiv
O caso para se livrar de CASE
Isso deve retornar um conjunto de dados conforme abaixo.
Results
UnPivID | UnPivYear | UnPivMonth | UnPivDate | UnPivDate2012 | val |
1 | 2010 | 1 | 01/01/2010 | 01/01/2010 | 1 |
1 | 2010 | 2 | 01/02/2010 | 01/02/2010 | 2 |
1 | 2010 | 3 | 01/03/2010 | 01/03/2010 | 3 |
1 | 2010 | 4 | 01/04/2010 | 01/04/2010 | 4 |
1 | 2010 | 5 | 01/05/2010 | 01/05/2010 | 5 |
1 | 2010 | 6 | 01/06/2010 | 01/06/2010 | 6 |
1 | 2010 | 7 | 01/07/2010 | 01/07/2010 | 7 |
1 | 2010 | 8 | 01/08/2010 | 01/08/2010 | 8 |
1 | 2010 | 9 | 01/09/2010 | 01/09/2010 | 9 |
1 | 2010 | 10 | 01/10/2010 | 01/10/2010 | 10 |
1 | 2010 | 11 | 01/11/2010 | 01/11/2010 | 11 |
1 | 2010 | 12 | 01/12/2010 | 01/12/2010 | 12 |
1 | 2011 | 1 | 01/01/2011 | 01/01/2011 | 1 |
1 | 2011 | 2 | 01/02/2011 | 01/02/2011 | 2 |
1 | 2011 | 3 | 01/03/2011 | 01/03/2011 | 3 |
1 | 2011 | 4 | 01/04/2011 | 01/04/2011 | 4 |
1 | 2011 | 5 | 01/05/2011 | 01/05/2011 | 5 |
1 | 2011 | 6 | 01/06/2011 | 01/06/2011 | 6 |
1 | 2011 | 7 | 01/07/2011 | 01/07/2011 | 7 |
1 | 2011 | 8 | 01/08/2011 | 01/08/2011 | 8 |
1 | 2011 | 9 | 01/09/2011 | 01/09/2011 | 9 |
1 | 2011 | 10 | 01/10/2011 | 01/10/2011 | 10 |
1 | 2011 | 11 | 01/11/2011 | 01/11/2011 | 11 |
1 | 2011 | 12 | 01/12/2011 | 01/12/2011 | 12 |
1 | 2012 | 1 | 01/01/2012 | 01/01/2012 | 1 |
1 | 2012 | 2 | 01/02/2012 | 01/02/2012 | 2 |
1 | 2012 | 3 | 01/03/2012 | 01/03/2012 | 3 |
1 | 2012 | 4 | 01/04/2012 | 01/04/2012 | 4 |
1 | 2012 | 5 | 01/05/2012 | 01/05/2012 | 5 |
1 | 2012 | 6 | 01/06/2012 | 01/06/2012 | 6 |
1 | 2012 | 7 | 01/07/2012 | 01/07/2012 | 7 |
1 | 2012 | 8 | 01/08/2012 | 01/08/2012 | 8 |
1 | 2012 | 9 | 01/09/2012 | 01/09/2012 | 9 |
1 | 2012 | 10 | 01/10/2012 | 01/10/2012 | 10 |
1 | 2012 | 11 | 01/11/2012 | 01/11/2012 | 11 |
1 | 2012 | 12 | 01/12/2012 | 01/12/2012 | 12 |
2 | 2010 | 1 | 01/01/2010 | 01/01/2010 | 1 |
2 | 2010 | 2 | 01/02/2010 | 01/02/2010 | 2 |
2 | 2010 | 3 | 01/03/2010 | 01/03/2010 | 3 |
2 | 2010 | 4 | 01/04/2010 | 01/04/2010 | 4 |
2 | 2010 | 5 | 01/05/2010 | 01/05/2010 | 5 |
2 | 2010 | 6 | 01/06/2010 | 01/06/2010 | 6 |
2 | 2010 | 7 | 01/07/2010 | 01/07/2010 | 7 |
2 | 2010 | 8 | 01/08/2010 | 01/08/2010 | 8 |
2 | 2010 | 9 | 01/09/2010 | 01/09/2010 | 9 |
2 | 2010 | 10 | 01/10/2010 | 01/10/2010 | 10 |
2 | 2010 | 11 | 01/11/2010 | 01/11/2010 | 11 |
2 | 2010 | 12 | 01/12/2010 | 01/12/2010 | 12 |