У цій статті надано повний огляд використання операторів PIVOT і UNPIVOT у SQL Server. Оператори PIVOT і UNPIVOT подібні до операторів відношення, які дозволяють перетворення табличного виразу в іншу таблицю . Обидва оператори створюють багатовимірні звіти, які допомагають швидко поєднувати та порівнювати великі обсяги даних.
Ми можемо використовувати Оператор PIVOT коли нам потрібно перетворити табличні вирази. Це розділяє унікальні значення з одного стовпця в багато стовпців в кінцевому результаті. Він також агрегати решту значень стовпців, необхідних для кінцевого результату. Оператор UNPIVOT перетворює дані зі стовпців табличного виразу на значення стовпця, що є зворотним до 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. Він перетворює дані з рядків у стовпці. Він розбиває унікальні значення з одного стовпця на багато стовпців, а потім агрегує значення стовпців, що залишилися, необхідні для кінцевого результату.
Щоб створити зведену таблицю, потрібно виконати наступні кроки:
дхармендра вік
- Виберіть базовий набір даних для зведення.
- Створення тимчасових результатів за допомогою похідної таблиці або CTE (загальний табличний вираз).
- Використовуйте оператор PIVOT.
Синтаксис
Наступний синтаксис ілюструє використання PIVOT у SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Якщо ми зламаємо цей сценарій, ми побачимо, що він складається з двох окремих розділів. Перший розділ вибирає дані з основної таблиці, а другий визначає, як буде побудована зведена таблиця. Друга частина також містить деякі спеціальні ключові слова, такі як SUM, FOR та IN. Давайте подивимося значення цих ключових слів в операторі PIVOT.
рядок java в масив
SUM
Цей оператор звик агрегувати значення із зазначеного стовпця, який буде використано у зведеній таблиці. Ми повинні використовувати його з оператором 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
Виконання цього оператора дасть наведені нижче результати. Тут ми можемо побачити розрахована сума продажів Північного та Південного регіонів, що відповідають значенням року .
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 дозволяє нам уникнути цієї проблеми, використовуючи квадратні дужки перед кожним цілим значенням. Оновлений оператор показаний у такому фрагменті коду:
ітерація списку в java
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. Але припустимо, що в майбутньому році кількість колонок буде збільшено. Розглядаючи попередній приклад, ми маємо 2010, 2011 і 2012 роки як стовпці PIVOT. Однак немає гарантії, що ці стовпці не зміняться в майбутньому. Що станеться, якщо ми матимемо дані за 2013 чи 2014 рік, а може навіть більше? У таких випадках нам потрібно буде використовувати динамічна зведена таблиця запитів, щоб вирішити цю проблему.
Динамічний запит до таблиці PIVOT інкапсулює весь сценарій PIVOT у збережену процедуру. Ця процедура забезпечить налаштування параметрів, що дозволить нам змінити наші вимоги, змінивши кілька параметризованих значень.
Наведений нижче код SQL пояснює роботу динамічної зведеної таблиці. У цьому сценарії ми спочатку отримали всі окремі значення зі стовпця 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 : ця змінна візьме назву стовпця з вихідної таблиці, на якій створено зведену таблицю. Наприклад , тут стовпець «Регіон» відображає всі регіони, доступні в стовпцях.
@PivotList : Ця змінна прийматиме список стовпців, який ми хочемо відобразити як вихідний стовпець у зведеній таблиці.
Виконання динамічної збереженої процедури
Після успішного створення динамічної збереженої процедури ми готові до її виконання. Наступний оператор використовується для виклику динамічної збереженої процедури для відображення зведеної таблиці під час виконання:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Тут ми вказали назву стовпця ' Регіон ' як перший параметр і список стовпців PIVOT як другий параметр. Виконання сценарію покаже такий результат:
Тепер ми можемо додати більше стовпців у майбутньому під час виконання, щоб відобразити зведену таблицю, що неможливо в перших двох прикладах.
підписка на azure
Оператор 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
Виконання фрагмента коду відобразить той самий результат:
java сон
ПРИМІТКА. Процес UNPIVOT є зворотною операцією процедури PIVOT, але не є точним. Оскільки рядки було об’єднано, коли PIVOT обчислює агрегат і об’єднує багато рядків в один рядок у результаті, операція UNPIVOT не може зробити таблицю схожою на вихідну. Однак, якщо оператор PIVOT не об’єднує багато рядків в один рядок, тоді оператор UNPIVOT може отримати вихідну таблицю з вихідних даних PIVOT.
Висновок
У цій статті надано повний огляд операторів PIVOT і UNPIVOT у SQL Server і перетворено вираз таблиці в інший. Ніколи не слід забувати, що UNPIVOT є зворотною операцією PIVOT, але це не є точною протилежністю результату PIVOT.