Тази статия ще даде пълен преглед на използването на операторите PIVOT и UNPIVOT в SQL Server. Операторите PIVOT и UNPIVOT са подобни на релационните оператори, които позволяват трансформиране на табличния израз в друга таблица . И двата оператора генерират многоизмерно отчитане, което помага за бързото комбиниране и сравняване на голямо количество данни.
Можем да използваме PIVOT оператор когато трябва да трансформираме изрази с таблични стойности. Той разделя на уникални стойности от една колона в много колони в крайния резултат. То също инертни материали останалите стойности на колоната, изисквани в крайния резултат. Оператор UNPIVOT преобразува данни от колони на табличен израз в стойности на колони, което е обратното на PIVOT.
Нека го разберем с помощта на простата диаграма, дадена по-долу:
регресионен израз в java
От лявата страна на тази фигура можем да видим оригинален набор от данни , който има три колони: Година, Регион, и Продажби . След това можем да видим PIVOT таблицата от дясната страна, която е конструирана чрез завъртане на Регион (редове) на север и юг (колони) . След като преобразуваме редове в колони, можем да направим съвкупност от стойности на колона Продажби за всяко пресичане между колоните и редовете на PIVOT таблицата.
Нека първо създадем таблица с име pivot_demo за демонстриране на операторите PIVOT и UNPIVOT. Следният оператор създава нова таблица в нашата посочена база данни:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
След това вмъкнете някои данни в тази таблица, както е показано по-долу:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Можем да проверим данните с помощта на израза SELECT. Ще получим изхода по-долу:
PIVOT оператор
Този оператор се използва за завъртане на таблични изрази. За първи път беше представен във версия SQL Server 2005. Той преобразува данни от редове в колони. Той разделя уникалните стойности от една колона на много колони и след това агрегира останалите стойности на колоните, необходими в крайния резултат.
Трябва да следваме следните стъпки, за да създадем PIVOT таблица:
- Изберете основния набор от данни за завъртане.
- Създайте временни резултати с помощта на извлечена таблица или CTE (общ табличен израз).
- Използвайте оператора PIVOT.
Синтаксис
Следният синтаксис илюстрира използването на PIVOT в SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ако прекъснем този скрипт, можем да видим, че той има две отделни секции. Първият раздел избира данни от основната таблица, а вторият раздел определя как ще бъде конструирана PIVOT таблицата. Втората част също съдържа някои специални ключови думи като SUM, FOR и IN. Нека видим значението на тези ключови думи в оператора PIVOT.
string.format в java
СУМА
Този оператор е свикнал обобщете стойностите от указаната колона, която да се използва в PIVOT таблицата. Трябва да го използваме с оператора PIVOT, за да получим дисплеите на обобщените колони за секциите със стойности.
ЗА Ключова дума
Тази ключова дума се използва за оператора на PIVOT таблицата инструктирайте оператора на PIVOT върху коя колона трябва да се приложи функцията PIVOT. По принцип той показва имената на колоните, които ще се трансформират от редове в колони.
IN Ключова дума
Тази ключова дума изброява всички уникални стойности от колоната PIVOT, за да се показват като колоните на таблицата PIVOT.
Пример
Нека го разберем с помощта на различни примери.
1. Следният оператор първо избира колоната Година, Север и Юг като базови данни за завъртане. След това създайте временен резултат, като използвате получената таблица и накрая приложете оператора PIVOT, за да генерирате крайния изход. Този изход също е подреден във възходящата година.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
Изпълнението на този оператор ще доведе до изхода по-долу. Тук можем да видим изчислена сума от продажбите в Северен и Южен регион, съответстваща на стойностите за годината .
cm във футове и инчове
2. Това е друг пример, при който ще изчислим сумата от продажби за всяка година, съответстваща на регионалните стойности:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
Изпълнението на това изявление ще генерира грешка защото не можем да посочим цифровата стойност като име на колона директно.
Въпреки това SQL Server ни позволява да избегнем този проблем, като използваме скобите преди всяка целочислена стойност. Актуализираното изявление е показано в следния кодов фрагмент:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Този отчет е изпълнен успешно и показва изчислената сума на продажбите за всяка година, съответстваща на стойностите на региона:
3. Предишният пример за получаване на PIVOT таблица е полезен, когато сме наясно с всички възможни стойности на PIVOT колона. Но да предположим, че през следващата година броят на колоните се увеличава. Имайки предвид предходния пример, имаме 2010, 2011 и 2012 години като PIVOT колони. Въпреки това, няма гаранция, че тези колони няма да се променят в бъдеще. Какво се случва, ако имаме данни от 2013 или 2014 г., или може би дори повече? В такива случаи ще трябва да използваме динамична PIVOT таблица запитвания за отстраняване на този проблем.
Заявката за динамична PIVOT таблица капсулира целия PIVOT скрипт в съхранена процедура. Тази процедура ще предостави регулируеми опции, което ни позволява да модифицираме нашите изисквания чрез промяна на няколко параметризирани стойности.
Следният SQL код обяснява работата на динамичната PIVOT таблица. В този скрипт първо сме извлекли всички отделни стойности от колоната PIVOT и след това сме написали SQL израз за изпълнение с PIVOT заявката по време на изпълнение. Нека да видим изхода след изпълнение на този скрипт:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
В този скрипт сме създали две параметризирани променливи. Описанието му е дадено по-долу:
@PivotColumn : Тази променлива ще вземе името на колоната от оригиналната таблица, върху която е създадена PIVOT таблицата. Например , тук колоната „Регион“ показва всички налични региони в колоните.
@PivotList : Тази променлива ще вземе списъка с колони, който искаме да покажем като изходна колона в PIVOT таблицата.
Изпълнение на динамична съхранена процедура
След успешното създаване на динамичната съхранена процедура, ние сме готови да я изпълним. Следният оператор се използва за извикване на динамичната съхранена процедура за показване на PIVOT таблицата по време на изпълнение:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Тук вече сме посочили името на колоната ' Регион ' като първи параметър и списъкът с PIVOT колони като втори параметър. Изпълнението на скрипта ще покаже следния резултат:
bash масиви
Сега можем да добавим повече колони в бъдеще по време на изпълнение, за да покажем PIVOT таблицата, което не е възможно с първите два примера.
Оператор UNPIVOT
Това е обратният метод на оператора PIVOT в SQL Server. Този оператор изпълнява обратна операция на PIVOT чрез преобразуване на данни от колони в редове. Операторът UNPIVOT също завърта PIVOT таблицата в обикновената таблица. За първи път беше представен във версия SQL Server 2005.
Синтаксис
Следният синтаксис илюстрира UNPIVOT в SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Пример
Нека разберем как да UNPIVOT операцията PIVOT с примери. Първо ще създадем оригинална таблица и PIVOT маса и след това приложи оператор UNPIVOT върху тази таблица.
Следният кодов фрагмент първо декларира временна променлива на таблица @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
След това ще вмъкнем стойности в тази таблица, както е показано по-долу:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Сега можем да извършим операция UNPIVOT, като използваме следния оператор:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Изпълнението на кодовия фрагмент ще върне следния резултат:
Кодовият фрагмент по-долу е друг пример за първо извършване на операция PIVOT и след това операция UNPIVOT на същата таблица в рамките на една заявка:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Изпълнението на кодовия фрагмент ще покаже същия резултат:
може ли абстрактен клас да има конструктор
ЗАБЕЛЕЖКА: Процесът UNPIVOT е обратна операция на процедурата PIVOT, но не е точно обръщане. Тъй като редовете са били обединени, когато PIVOT изчислява агрегата и е комбинирал много редове в един ред в резултата, следователно операцията UNPIVOT не може да направи таблицата като оригинала. Ако обаче операторът PIVOT не обедини много редове в един ред, тогава операторът UNPIVOT може да получи оригиналната таблица от изхода на PIVOT.
Заключение
Тази статия ще даде пълен преглед на операторите PIVOT и UNPIVOT в SQL Server и ще преобразува табличен израз в друг. Никога не трябва да забравяме, че UNPIVOT е обратната операция на PIVOT, но не е точно обратното на резултата от PIVOT.