Ms sql pivot пример

Ms sql pivot пример

Сегодня мы поговорим о таких операторах Transact-SQL как PIVOT и UNPIVOT, узнаем, для чего они нужны, рассмотрим синтаксис написания запросов, и, конечно же, разберем примеры использования их на практике.

В Transact-SQL для написания перекрестных запросов или кросс табличных выражений существует специальный оператор, я бы сказал даже целая конструкция под названием PIVOT, которая имеет достаточно специфический синтаксис, также существует оператор, который делает и обратное действие он называется как не странно UNPIVOT. Эти операторы мы сейчас подробно рассмотрим, и для начала давайте я расскажу, как будет выглядеть данная статья.

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

Примечание! Все примеры мы будем рассматривать в СУБД MS SQL Server 2014 Express с использованием Management Studio.

Оператор PIVOT

PIVOT – это оператор Transact-SQL, который поворачивает результирующий набор данных, т.е. происходит транспонирование таблицы, при этом используются агрегатные функции, и данные соответственно группируются. Другими словами, значения, которые расположены по вертикали, мы выстраиваем по горизонтали.

Данный оператор может потребоваться тогда, когда необходимо, например, предоставить какой либо отчет в наглядной форме по годам, допустим для бухгалтеров и экономистов, так как именно они любят представления данных в таком виде. Также он может пригодиться и просто для преставления какой-либо статистики, но в любом случае из собственного опыта могу сказать, что оператор PIVOT будет требоваться достаточно редко, но когда он потребуется он будет просто незаменим и очень полезен, поэтому Вы должны знать, как и когда его можно использовать.

Результат, который мы получим при использовании оператора PIVOT, можно также получить и с использованием известной конструкции select…case, а до появления MS SQL сервера 2005 только с использованием этой конструкции, как Вы правильно поняли, оператор PIVOT можно использовать, только начиная с 2005 sql сервера.

У данного оператора очень специфический, непривычный и некоторые даже скажут сложный синтаксис, как для написания, так и для простого понимания.

Синтаксис оператора PIVOT

SELECT столбец для группировки, [значения по горизонтали],…

FROM таблица или подзапрос

FOR столбец, содержащий значения, которые станут именами столбцов

IN ([значения по горизонтали],…)

)AS псевдоним таблицы (обязательно)

в случае необходимости ORDER BY;

Вот такой вот синтаксис, помимо всего прочего значения, которые будут выступать в качестве названия колонок по горизонтали, необходимо писать вручную, т.е. мы должны знать их заранее, другими словами, динамический запрос построить, не получиться. На самом деле можно, с помощью динамического формирования строки запроса, а потом исполнение этой строки через специальную команду EXECUTE, но как говорится это уже совсем другая история (для начинающих рекомендую прочитать наш справочник по Transact-SQL).

Пример использования оператора PIVOT

С теорией я думаю достаточно, поэтому давайте переходить к практике, тем более что на примерах лучше понять, как же работает этот оператор.

И для начала давайте разберем исходные данные.

Допустим, у нас есть таблица вот с такой структурой:

Где, fio — это ФИО сотрудника, god – год, в котором он получал премию, summa — соответственно сумма премии, вот такой незамысловатый пример, так как в плоскости времени наглядней видна работа оператора PIVOT.

И в данной таблице у нас есть тестовые данные, для просмотра этих данных напишем простой запрос на выборку, т.е. select

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

Читайте также:  Аттила тотал вар энциклопедия

Самым простым способом будет конечно просто использовать конструкцию GROUP BY, например

На что нам начальник скажет, что это такое? ничего не понятно? не наглядно? Улучшить ситуацию можно, добавив еще и сортировку ORDER BY, допустим сначала по фамилии, а затем по году

но это все равно не то. А вот если мы будем использовать оператор PIVOT, например вот таким образом

то у нас получится вот такой результат

Я думаю, Вы согласитесь, что так намного наглядней и понятней.

  • fio — столбец, по которому мы будем осуществлять группировку;
  • [2011],[2012],[2013],[2014],[2015] — названия наших столбцов по горизонтали, ими выступают значения из колонки god;
  • sum(summa) — агрегатная функция по столбцу summa;
  • for god in ([2011],[2012],[2013],[2014],[2015]) — тут мы указываем колонку, в которой содержатся значения, которые будут выступать в качестве названия наших результирующих столбцов, по факту в скобках мы указываем то же самое, что и чуть выше в select;
  • as test_pivot — это обязательный псевдоним, не забывайте его указывать, иначе будет ошибка.

Переходим к UNPIVOT.

Оператор UNPIVOT

UNPIVOT – это оператор Transact-SQL, который выполняет действия, обратные PIVOT. Сразу скажу, что да он разворачивает таблицу в обратную сторону, но в отличие от оператора PIVOT он ничего не агрегирует и уж тем более не раз агрегирует.

UNPIVOT требуется еще реже, чем PIVOT, но о нем также необходимо знать.

Здесь я думаю, давайте сразу перейдем к рассмотрению примера.

Пример использования UNPIVOT

Допустим, таблица имеет следующую структуру:

Где, fio — ФИО сотрудника, а number1, number2… и так далее это какие-то номера этого сотрудника:)

Данные будут, например, такие:

И допустим, нам необходимо развернуть эту таблицу, для этого мы будем использовать оператор UNPIVOT, а запрос будет выглядеть следующим образом:

  • fio – столбец с ФИО, он в принципе не изменился;
  • column_name – псевдоним столбца, который будет содержать названия наших колонок;
  • number – псевдоним для значений из столбцов number1, number2…

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие возможности языка Transact-SQL.

У меня есть запрос с кучей лефтджойнов.

Создала запрос для вывода "прямых" данных, выводит:

Мне нужно отобразить следующим образом:

Вообще не понимаю, как ПРАВИЛЬНО описать pivot, все примеры какие-то слишком простые, а у меня запрос 5 джойнами и 6 условиями. Можете, пожалуйста, на этом примере показать и описать..

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Реляционные операторы PIVOT и UNPIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT поворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. В случае необходимости PIVOT также объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных. And PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT выполняет действия, обратные PIVOT, преобразуя столбцы возвращающего табличное значение выражения в значения столбца. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Читайте также:  Android studio string to int

Синтаксис PIVOT является более простым и понятным, чем синтаксис, который может выполнить то же действие с помощью последовательности инструкций SELECT. CASE . The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT. CASE statements. Полное описание синтаксиса инструкции PIVOT см. в разделе FROM (Transact-SQL). For a complete description of the syntax for PIVOT , see FROM (Transact-SQL).

Синтаксис Syntax

В следующем синтаксисе приводится использование оператора PIVOT . The following syntax summarizes how to use the PIVOT operator.

Примечания Remarks

Идентификаторы столбцов в предложении UNPIVOT следуют параметрам сортировки каталога. The column identifiers in the UNPIVOT clause follow the catalog collation. Для База данных SQL SQL Database параметры сортировки всегда соответствуют SQL_Latin1_General_CP1_CI_AS . For База данных SQL SQL Database , the collation is always SQL_Latin1_General_CP1_CI_AS . Для частично автономных баз данных SQL Server SQL Server параметры сортировки всегда соответствуют Latin1_General_100_CI_AS_KS_WS_SC . For SQL Server SQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC . Если столбец используется в сочетании с другими столбцами, для предотвращения конфликтов требуется предложение collate ( COLLATE DATABASE_DEFAULT ). If the column is combined with other columns, then a collate clause ( COLLATE DATABASE_DEFAULT ) is required to avoid conflicts.

Базовый пример PIVOT Basic PIVOT Example

В следующем примере кода создается таблица, включающая два столбца и четыре строки. The following code example produces a two-column table that has four rows.

Результирующий набор: Here is the result set.

Для значения DaysToManufacture , равного трем, продукты не определены. No products are defined with three DaysToManufacture .

Следующий код отображает тот же самый результат, сведенный так, что значения DaysToManufacture становятся заголовками. The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. Для значения трех [3] дней приводится столбец, даже если результат равен NULL . A column is provided for three [3] days, even though the results are NULL .

Результирующий набор: Here is the result set.

Сложный пример PIVOT Complex PIVOT Example

Обычно оператор PIVOT может быть полезен при создании отчетов с перекрестными ссылками для предоставления сводки по данным. A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data. Например, пусть необходимо обратиться к таблице PurchaseOrderHeader образца базы данных AdventureWorks2014 для определения количества заказов на покупку, размещенных некоторым сотрудником. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. Требуемые данные, отсортированные по поставщикам, можно извлечь при выполнении следующего запроса. The following query provides this report, ordered by vendor.

Здесь приводится частичный результирующий набор. Here is a partial result set.

Данные, возвращаемые в результате выполнения указанного подзапроса выборки, сводятся в столбец EmployeeID . The results returned by this subselect statement are pivoted on the EmployeeID column.

Уникальные значения столбца EmployeeID становятся полями итогового результирующего набора. The unique values returned by the EmployeeID column become fields in the final result set. Таким образом, имеется столбец, соответствующий каждому идентификатору EmployeeID , указанному в предложении PIVOT: в данном случае это сотрудники 250 , 251 , 256 , 257 и 260 . As such, there’s a column for each EmployeeID number specified in the pivot clause: in this case employees 250 , 251 , 256 , 257 , and 260 . PurchaseOrderID служит столбцом значений, по которому группируются столбцы, возвращаемые в конечный вывод и называемые столбцами группирования. The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. В этом случае значения столбцов группирования обрабатываются с помощью функции COUNT . In this case, the grouping columns are aggregated by the COUNT function. Обратите внимание, что при вычислении функции COUNT для каждого сотрудника появится предупреждающее сообщение о том, что значения NULL столбца PurchaseOrderID не учитываются. Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column weren’t considered when computing the COUNT for each employee.

Читайте также:  Joomla дополнительные поля материала

При статистической обработке данных с использованием агрегатных функций, содержащих оператор PIVOT , пустые значения столбцов не учитываются. When aggregate functions are used with PIVOT , the presence of any null values in the value column are not considered when computing an aggregation.

Оператор UNPIVOT выполняет действия, обратные оператору PIVOT , преобразуя столбцы данных в строки. UNPIVOT carries out almost the reverse operation of PIVOT , by rotating columns into rows. Допустим, что таблица, созданная в ходе выполнения предыдущего примера, хранится в базе данных и имеет идентификатор pvt . Пусть необходимо преобразовать идентификаторы столбцов Emp1 , Emp2 , Emp3 , Emp4 и Emp5 в строки данных, сгруппированные по поставщикам. Suppose the table produced in the previous example is stored in the database as pvt , and you want to rotate the column identifiers Emp1 , Emp2 , Emp3 , Emp4 , and Emp5 into row values that correspond to a particular vendor. Таким образом, необходимо определить два дополнительных столбца. As such, you must identify two additional columns. Столбец, содержащий значения поворачиваемых столбцов ( Emp1 , Emp2 . ), будет называться Employee , а столбец, который будет содержать значения, в данный момент существующие в поворачиваемых столбцах, будет называться Orders . The column that will contain the column values that you’re rotating ( Emp1 , Emp2 . ) will be called Employee , and the column that will hold the values that currently exist under the columns being rotated will be called Orders . Указанные столбцы связаны соответственно с такими параметрами в определении на языке Transact-SQL Transact-SQL , как pivot_column и value_column. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL Transact-SQL definition. Вот запрос. Here is the query.

Здесь приводится частичный результирующий набор. Here is a partial result set.

Обратите внимание, что оператор UNPIVOT не является в точности обратным оператору PIVOT . Notice that UNPIVOT isn’t the exact reverse of PIVOT . Оператор PIVOT выполняет статистическую обработку и слияние нескольких строк в единую выходную строку. PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output. Оператор UNPIVOT не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены. UNPIVOT doesn’t reproduce the original table-valued expression result because rows have been merged. Кроме того, оператор UNPIVOT удаляет значения NULL из обрабатываемых им данных. Also, null values in the input of UNPIVOT disappear in the output. Поэтому в случае наличия в исходных столбцах значений NULL данные на выходе могут отличаться от данных до их обработки с помощью оператора PIVOT . When the values disappear, it shows that there may have been original null values in the input before the PIVOT operation.

Ссылка на основную публикацию
Adblock detector