SQL Pivot - знайте, как преобразовывать строки в столбцы

Реляционный хранить огромные объемы данных в виде таблиц. Эти таблицы могут иметь любое количество строк и столбцов. Но что, если бы вам пришлось изменить данные на уровне строк на данные в столбцах? Что ж, в этой статье о SQL Pivot я покажу вам, как преобразовать строки в столбец на SQL Server.

В этой статье будут рассмотрены следующие темы:

• Что такое PIVOT в SQL?
• Синтаксис
• пример
• Работа пункта PIVOT
• SQL UNPIVOT

Что такое PIVOT в SQL?

PIVOT используется для поворота табличного значения путем преобразования уникальных значений одного столбца в несколько столбцов. Он используется для преобразования строк в значения столбцов и при необходимости выполняет агрегаты для оставшихся значений столбцов.

UNPIVOT, с другой стороны, используется для выполнения противоположных операций. Таким образом, он используется для преобразования столбцов определенной таблицы в значения столбцов.

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

Синтаксис:

SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query that returns the data) AS [alias для начального запроса] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName столбца, значения которого станут заголовками столбцов] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last сводный столбец])) AS [псевдоним для сводной таблицы]

Вот,вы также можете использовать Предложение ORDER BY для сортировки значений в порядке возрастания или убывания. Теперь, когда вы знаете, что такое PIVOT в SQL и его базовый синтаксис, давайте продвинемся вперед и посмотрим, как его использовать.

Примеры

Для вашего лучшего понимания я рассмотрю следующую таблицу, чтобы объяснить вам все примеры.

Таблица поставщиков:

Давайте напишем простой запрос, чтобы получить средние затраты, потраченные каждым покупателем.

ВЫБЕРИТЕ CustomerID, AVG (Cost) AS AverageCostofCustomer FROM Suppliers GROUP BY CustomerID

Вывод:

Теперь допустим, что мы хотим повернуть приведенную выше таблицу. Здесь значения столбца CustomerID станут заголовками столбцов.

- Создайте сводную таблицу с одной строкой и тремя столбцами. ВЫБЕРИТЕ 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (ВЫБЕРИТЕ идентификатор клиента, затраты от поставщиков) AS SourceTable PIVOT (AVG (Cost) FOR CustomerID IN ( [11], [22], [33])) как сводная таблица

Вывод:

Заметка: Когда вы используете агрегатные функции с PIVOT нулевые значения не учитываются при вычислении агрегирования.

Что ж, это был базовый пример, но давайте теперь разберемся, как работает предложение PIVOT.

Работа пункта PIVOT

Как вы можете ссылаться выше, для создания Сводной таблицы вам необходимо выполнить следующие шаги:

• Выберите столбцы для поворота
• Затем выберите исходную таблицу.
• Примените оператор PIVOT, а затем используйте агрегатные функции.
• Упомяните основные значения.

Выберите столбцы для поворота

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

ВЫБЕРИТЕ 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33]

Выбрать исходную таблицу

Затем вы должны указать оператор SELECT, который вернет исходные данные для сводной таблицы. В нашем примере мы возвращаем CustomerID и Cost из таблицы Suppliers.

(ВЫБЕРИТЕ CustomerID, Стоимость от поставщиков) AS SourceTable

Примените оператор PIVOT, а затем используйте агрегатные функции

Затем вы должны указать агрегатную функцию, которая будет использоваться при создании сводной таблицы. В нашем примере я использовал функцию AVG для расчета средней стоимости.

серия фибоначчи c ++

PIVOT (СРЕД. (Стоимость)

Упомяните сводные значения

Наконец, вы должны указать значения, которые должны быть включены в итоговую сводную таблицу. Эти значения будут использоваться в качестве заголовков столбцов в сводной таблице.

FOR 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 sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Вывод:

Теперь, допустим, мы хотим развернуть таблицу. Для этого вы можете обратиться к следующему коду:

ВЫБЕРИТЕ SupplierID, клиенты, продукты ИЗ (ВЫБЕРИТЕ SupplierD, AAA, BBB, CCC ИЗ выборочной таблицы) p UNPIVOT (Продукты ДЛЯ клиентов IN (AAA, BBB, CCC)) В качестве примера GO

Вот как можно использовать SQL PIVOT и UNPIVOT. На этом мы подошли к концу данной статьи. Надеюсь, вы поняли, как пользоваться SQL. Если вы хотите узнать больше о MySQL и познакомьтесь с этой реляционной базой данных с открытым исходным кодом, а затем ознакомьтесь с нашими который включает в себя живое обучение под руководством инструктора и опыт реальных проектов. Этот тренинг поможет вам глубже понять MySQL и достичь мастерства в этой теме.

Есть вопрос к нам? Пожалуйста, укажите это в разделе комментариев к этой статье о SQL Pivot, и я вернусь к вам.