SQL Pivot - знайте как да конвертирате редове в колони

Релационна съхранявайте огромни количества данни под формата на таблици. Тези таблици могат да имат произволен брой редове и колони. Но какво, ако трябва да промените данните на ниво ред на колони? Е, в тази статия за SQL Pivot ще ви покажа как можете да конвертирате редове в колона в SQL Server.

отидете сортирайте c ++

В тази статия ще бъдат разгледани следните теми:

• Какво е PIVOT в SQL?
• Синтаксис
• Пример
• Работа на клауза PIVOT
• SQL UNPIVOT

Какво е PIVOT в SQL?

PIVOT се използва за завъртане на табличната стойност чрез преобразуване на уникалните стойности на една колона в множество колони. Използва се за завъртане на редовете до стойности на колони и изпълнява агрегиране, когато се изисква за останалите стойности на колони.

UNPIVOT, от друга страна, се използва за извършване на противоположни операции. Така че, той се използва за преобразуване на колоните на конкретна таблица в стойности на колони.

Продължавайки в тази статия, нека разберем синтаксиса на SQL Pivot.

Синтаксис:

ИЗБЕРЕТЕ NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT заявка, която произвежда данните) за първоначалната заявка] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName на колоната, чиито стойности ще станат заглавки на колони] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last въртяща се колона])) КАТО [псевдоним за обобщената таблица]

Тук,можете също да използвате Клауза ORDER BY за сортиране на стойностите във възходящ или низходящ ред. След като вече знаете какво е PIVOT в SQL и основния му синтаксис, нека да продължим напред и да видим как да го използваме.

Примери

За ваше по-добро разбиране ще разгледам следната таблица, за да ви обясня всички примери.

Таблица на доставчика:

Нека напишем проста заявка, за да извлечем средните разходи, похарчени от всеки клиент.

ИЗБЕРЕТЕ ИДЕНТИФИКАТОР, СРЕДЕН (Разход) КАТО СРЕДНИ ЦЕНИ НА ПОТРЕБИТЕЛЯ ОТ ГРУПА НА ДОСТАВЧИЦИТЕ ПО КЛИЕНТИ

Изход:

Сега, нека кажем, че искаме да въртим горната таблица. Тук стойностите на колоните на CustomerID ще станат заглавки на колоните.

- Създайте обобщена таблица с един ред и три колони SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Suppliers) AS SourceTable PIVOT (AVG (Cost) FOR CustomerID IN ( [11], [22], [33])) КАТО обобщена таблица

Изход:

Забележка: Когато използвате агрегирани функции с PIVOT, нулевите стойности не се вземат предвид при изчисляване на агрегиране.

Е, това беше основен пример, но нека сега разберем как работи клаузата PIVOT.

Работа на клаузата PIVOT

Както можете да се обърнете по-горе, за да създадете ПИВОТНА МАСА, трябва да следвате стъпките по-долу:

• Изберете колони за въртене
• След това изберете таблица източник.
• Приложете оператора PIVOT и след това използвайте агрегатните функции.
• Споменете пивотните стойности.

Изберете колони за въртене

Първоначално трябва да посочим полетата, които да бъдат включени в нашите резултати. В нашия пример разгледах колоната AverageCostofCustomer в обобщената таблица. След това създадохме три други колони със заглавките на колоните 11, 22 и 33. Пример-

ИЗБЕРЕТЕ 'AverageCostofCustomer' КАТО Cost_According_To_Customers, [11], [22], [33]

Изберете таблица източник

След това трябва да посочите оператора SELECT, който ще върне изходните данни за обобщената таблица. В нашия пример ние връщаме идентификатора на клиента и разходите от таблицата на доставчиците.

(ИЗБЕРЕТЕ ИД на клиента, цена ОТ ДОСТАВЧИЦИТЕ) КАТО Изходна таблица

Приложете оператора PIVOT и след това използвайте агрегатните функции

След това трябва да посочите обобщената функция, която да се използва при създаването на обобщена таблица. В нашия пример използвах функцията AVG за изчисляване на средната цена.

PIVOT (AVG (цена))

Споменете пивотните стойности

И накрая, трябва да споменете стойностите, които трябва да бъдат включени в получената обобщена таблица. Тези стойности ще се използват като заглавия на колоните в обобщената таблица.

ЗА CustomerID IN ([11], [22], [33])) КАТО обобщена таблица

Ето как работят операторите PIVOT. Продължавайки в тази статия за SQL PIVOT, нека разберем колко е различен от SQL UNPIVOT.

SQL UNPIVOT

Операторът SQL UNPIVOT се използва за извършване на операция, обратна на тази на PIVOT. Използва се за завъртане на данните на колоната в данни на ниво ред. Синтаксисът на UNPIVOT е подобен на този на PIVOT. Единствената разлика е, че трябва да използвате ' UNPIVOT ” .

Пример:

Нека създадем таблица с колоните SupplierID, AAA, BBB и CCC. Също така вмъкнете няколко стойности.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampleable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) ВЪРВЕТЕ

Изход:

Нека кажем, че искаме да депилираме таблицата. За да направите това, можете да се обърнете към следния код:

ИЗБЕРЕТЕ ИД на доставчик, Клиенти, Продукти ОТ (ИЗБЕРЕТЕ доставчика D, AAA, BBB, CCC ОТ пробната таблица) p UNPIVOT (Продукти ЗА КЛИЕНТИ В (AAA, BBB, CCC)) КАТО пример GO

Ето как можете да използвате SQL PIVOT и UNPIVOT. С това стигаме до края на тази статия. Надявам се, че сте разбрали как да използвате SQL. Ако искате да научите повече за MySQL и опознайте тази релационна база данни с отворен код, след това разгледайте нашата което се предлага с обучение под ръководството на инструктори на живо и опит в реалния живот на проекти. Това обучение ще ви помогне да разберете MySQL задълбочено и ще ви помогне да постигнете майсторство по темата.

Имате въпрос към нас? Моля, споменете го в раздела за коментари на тази статия за SQL Pivot и ще се свържа с вас.