SQL Server использует UNPIVOT для преобразования столбцов в строки
SQL Server, использующий функцию UNPIVOT в сценарии реального мира.
Кейс по избавлению от CASE
Многие из проектов, над которыми мы работали, включали работу с данными, хранящимися в таблице с месяцами с 1 по 12, хранящимися по годам. Это не редкое решение, но оно может привести к тому, что операторы SQL раздуваются десятками операторов CASE.
Эти операторы CASE затрудняют сопровождение, а также могут быть источником человеческих ошибок.
Ниже мы создали простой набор данных в соответствии с реальным примером, а в SQL Server есть функция, которая превращает столбцы в строки.
| 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 |
Кейс по избавлению от CASE
Если вы используете SQL Server ниже 2012, вам нужно будет создать эту функцию, которая является эквивалентом функции DATEFROMPARTS, встроенной с 2012 года.
Кейс по избавлению от CASE
Затем мы можем создать временную таблицу и заполнить ее данными из примера выше.
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Кейс по избавлению от CASE
Это должно вернуть набор данных, как показано ниже.
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 |
