Овај чланак ће дати потпуни преглед коришћења ПИВОТ и УНПИВОТ оператора у СКЛ Серверу. Оператори ПИВОТ и УНПИВОТ су слични релационим операторима који дозвољавају трансформисање израза са табелом вредношћу у другу табелу . Оба оператера генеришу вишедимензионално извештавање које помаже да се брзо комбинују и упореде велике количине података.
Можемо користити ПИВОТ оператор када треба да трансформишемо изразе са табеларним вредностима. То цепа јединствене вредности из једне колоне у више колона у коначном резултату. Такође агрегати преостале вредности колоне потребне у коначном резултату. УНПИВОТ оператор претвара податке из колона израза са табелом у вредности колона, што је обрнуто од ПИВОТ.
Хајде да то разумемо уз помоћ једноставног дијаграма датог у наставку:
На левој страни ове фигуре можемо видети оригинални скуп података , који има три колоне: година, регион, и Продаја . Затим можемо видети ПИВОТ табелу на десној страни, која је конструисана ротацијом Регион (редови) на север и југ (колоне) . Након претварања редова у колоне, можемо да урадимо збир вредности колоне Продаја за сваки пресек између колона и редова ПИВОТ табеле.
Хајде да прво направимо табелу са именом пивот_демо да демонстрира ПИВОТ и УНПИВОТ операторе. Следећа изјава креира нову табелу у нашој наведеној бази података:
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);
Податке можемо верификовати помоћу наредбе СЕЛЕЦТ. Добићемо доњи излаз:
ПИВОТ Оператор
Овај оператор се користи за ротирање израза са вредностима табеле. Први пут је представљен у верзији СКЛ Сервера 2005. Конвертује податке из редова у колоне. Он дели јединствене вредности из једне колоне у више колона, а затим агрегира преостале вредности колоне које су потребне у коначном резултату.
фибоначијев низ јава
Морамо да следимо следеће кораке да бисмо направили ПИВОТ табелу:
- Изаберите основни скуп података за окретање.
- Креирајте привремене резултате уз помоћ изведене табеле или ЦТЕ (израз заједничке табеле).
- Искористите ПИВОТ оператор.
Синтакса
Следећа синтакса илуструје употребу ПИВОТ-а у СКЛ Серверу:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ако разбијемо ову скрипту, можемо видети да има два одвојена одељка. Први одељак бира податке из главне табеле, а други одељак одређује како ће се ПИВОТ табела конструисати. Други део такође садржи неке посебне кључне речи као што су СУМ, ФОР и ИН. Хајде да видимо значење ових кључних речи у ПИВОТ оператору.
СУМ
Овај оператер је навикао да агрегирати вредности из наведене колоне која ће се користити у табели ПИВОТ. Морамо га користити са ПИВОТ оператором да бисмо добили агрегиране приказе колона за одељке вредности.
ФОР Кеиворд
Ова кључна реч се користи за исказ ПИВОТ табеле за упути оператера ПИВОТ на којој колони треба применити функцију ПИВОТ. У основи, то указује на имена колона која ће се трансформисати из редова у колоне.
ИН Кеиворд
Ова кључна реч наводи све јединствене вредности из колоне ПИВОТ која ће бити приказана као колоне ПИВОТ табеле.
Пример
Хајде да то разумемо уз помоћ разних примера.
1. Следећа изјава прво бира колону Година, Север и Југ као основне податке за окретање. Затим креирајте привремени резултат користећи изведену табелу и на крају примените ПИВОТ оператор да генеришете коначни излаз. Овај излаз је такође наручен у узлазној години.
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
Извршавање ове изјаве ће произвести доњи излаз. Овде можемо видети израчунати збир продаје северног и јужног региона који одговара вредностима године .
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;
Извршење ове изјаве ће произвести грешку јер не можемо директно навести нумеричку вредност као име колоне.
Међутим, СКЛ Сервер нам омогућава да избегнемо овај проблем коришћењем заграда испред сваке вредности целог броја. Ажурирана изјава је приказана у следећем исечку кода:
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. Претходни пример добијања ПИВОТ табеле је од помоћи када смо свесни свих могућих вредности ПИВОТ колоне. Али претпоставимо да се у наредној години број колона повећа. Узимајући у обзир претходни пример, имамо 2010, 2011 и 2012 године као ПИВОТ колоне. Међутим, не постоји гаранција да се ове колоне неће мењати у будућности. Шта се дешава ако имамо податке из 2013. или 2014. или можда чак и више? У таквим случајевима, мораћемо да користимо динамичка ПИВОТ табела упите за решавање овог проблема.
Упит динамичке ПИВОТ табеле обухвата целу ПИВОТ скрипту у ускладиштеној процедури. Ова процедура ће обезбедити прилагодљиве опције, омогућавајући нам да изменимо наше захтеве променом неколико параметризованих вредности.
модификаторски тастери
Следећи СКЛ код објашњава рад динамичке ПИВОТ табеле. У овој скрипти, прво смо преузели све различите вредности из колоне ПИВОТ, а затим написали СКЛ наредбу за извршавање са ПИВОТ упитом у време извршавања. Хајде да видимо излаз након извршавања ове скрипте:
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
У овој скрипти смо креирали две параметризоване променљиве. Његов опис је дат у наставку:
@ПивотЦолумн : Ова променљива ће преузети име колоне из оригиналне табеле на којој је креирана ПИВОТ табела. На пример , овде, колона 'Регион' приказује све регионе доступне у колонама.
@ПивотЛист : Ова променљива ће узети листу колона коју желимо да прикажемо као излазну колону у ПИВОТ табели.
Извршење динамичке ускладиштене процедуре
Након успешног креирања динамичке ускладиштене процедуре, спремни смо да је извршимо. Следећи исказ се користи за позивање динамичке ускладиштене процедуре за приказ ПИВОТ табеле у време извршавања:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Овде смо сада навели име колоне ' Регион ' као први параметар и листа ПИВОТ колона као други параметар. Извршавање скрипте ће приказати следећи излаз:
Сада можемо да додамо још колона у будућности током извршавања да бисмо приказали ПИВОТ табелу, што није могуће са прва два примера.
УНПИВОТ Оператор
То је обрнути метод ПИВОТ оператора у СКЛ Серверу. Овај оператер обавља супротан рад ПИВОТ-а претварањем података из колона у редове. Оператор УНПИВОТ такође ротира ПИВОТ табелу у регуларну табелу. Први пут је представљен у верзији СКЛ Сервера 2005.
Синтакса
јава до вхиле пример
Следећа синтакса илуструје УНПИВОТ у СКЛ Серверу:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Пример
Хајде да разумемо како да ПОНИШТИМО операцију ПИВОТ са примерима. Прво ћемо креирати ан оригинална табела и ПИВОТ табела а затим применио оператор УНПИВОТ на ову табелу.
Следећи исечак кода прво декларише привремену променљиву табеле @Таб:
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
Сада можемо да извршимо УНПИВОТ операцију користећи следећу изјаву:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Извршавање исечка кода ће вратити следећи излаз:
Исечак кода у наставку је још један пример да се прво изврши ПИВОТ операција, а затим УНПИВОТ операција на истој табели у оквиру једног упита:
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
Извршавање исечка кода ће приказати исти излаз:
НАПОМЕНА: УНПИВОТ процес је обрнута операција од ПИВОТ процедуре, али није тачан преокрет. Пошто су редови спојени када ПИВОТ израчуна агрегат и комбинује много редова у један ред у резултату, према томе, операција УНПИВОТ не може учинити табелу сличном оригиналу. Међутим, ако ПИВОТ оператор не споји много редова у један ред, онда УНПИВОТ оператор може добити оригиналну табелу из ПИВОТ излаза.
Закључак
Овај чланак ће дати потпуни преглед ПИВОТ и УНПИВОТ оператора у СКЛ Серверу и конвертовати табеларни израз у други. Никада не треба заборавити да је УНПИВОТ обрнута операција ПИВОТ-а, али није тачна инверзна ПИВОТ резултата.