Sql unpivot пример: PIVOT и UNPIVOT в Transact-SQL – описание и примеры использования операторов | Info-Comp.ru

PIVOT и UNPIVOT в Transact-SQL – описание и примеры использования операторов | Info-Comp.ru

Сегодня мы поговорим о таких операторах 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 таблица или подзапрос

PIVOT(агрегатная функция

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

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

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

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

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

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

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

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

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

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

 
   CREATE TABLE [dbo].[test_table_pivot](
        [fio] [varchar](50) NULL,
        [god] [int] NULL,
        [summa] [float] NULL
   ) ON [PRIMARY]
   GO

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

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

  
   SELECT * FROM dbo.test_table_pivot

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

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

  
   SELECT fio, god, sum(summa) AS summa 
   FROM dbo.test_table_pivot
   GROUP BY fio, god

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

  
   SELECT fio, god, sum(summa) as summa 
   FROM dbo.test_table_pivot
   GROUP BY fio, god
   ORDER BY fio, god

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

   
   SELECT fio, [2011], [2012], [2013], [2014], [2015]
   FROM dbo.test_table_pivot
   PIVOT (SUM(summa)for god in ([2011],[2012],[2013],[2014],[2015])
           ) AS test_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

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

   
   CREATE TABLE [dbo].[test_table_unpivot](
        [fio] [varchar](50) NULL,
        [number1] [int] NULL,
        [number2] [int] NULL,
        [number3] [int] NULL,
        [number4] [int] NULL,
        [number5] [int] NULL,
   ) ON [PRIMARY]
   GO

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

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

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

  
   SELECT fio, column_name, number
   FROM dbo.test_table_unpivot
   UNPIVOT(
          number for column_name in (
                                     [number1],[number2],[number3],[number4],[number5]
                                     )
          )AS test_unpivot

Где,

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

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

Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Видеоурок

На этом все, удачи!

Нравится2Не нравится

UNPIVOT / Хабр

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

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

Оптимизация – это, в первую очередь, поиск оптимального плана запроса. Однако, что делать в ситуации, когда стандартная конструкция языка выдает план, который очень далек от оптимального?

С такого рода проблемой я столкнулся, когда применял конструкцию UNPIVOT для преобразования столбцов в строки.

Путем небольшого сравнительного анализа, для UNPIVOT была найдена более эффективная альтернатива.

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

IF OBJECT_ID('dbo.UserBadges', 'U') IS NOT NULL
	DROP TABLE dbo.UserBadges
GO

CREATE TABLE dbo.UserBadges (
	  UserID INT
	, Gold SMALLINT NOT NULL
	, Silver SMALLINT NOT NULL
	, Bronze SMALLINT NOT NULL
	, CONSTRAINT PK_UserBadges PRIMARY KEY (UserID)
)

INSERT INTO dbo.UserBadges (UserID, Gold, Silver, Bronze)
VALUES
	(1, 5, 3, 1),
	(2, 0, 8, 1),
	(3, 2, 4, 11)

Взяв за основу эту таблицу, приведем различные методы преобразования столбцов в строки, а также планы их выполнения.

1. UNION ALL

В свое время, SQL Server 2000 не предоставлял эффективного способа преобразовывать столбцы в строки. Вследствие чего широко практиковалась практика многократной выборки из одной и той же таблицы, но с разным набором столбцов, объединенных через конструкцию UNION ALL:

SELECT UserID, BadgeCount = Gold, BadgeType = 'Gold' 
FROM dbo.UserBadges
    UNION ALL
SELECT UserID, Silver, 'Silver' 
FROM dbo.UserBadges
    UNION ALL
SELECT UserID, Bronze, 'Bronze' 
FROM dbo.UserBadges

Огромным минус этого подхода — повторные чтения данных, которые существенно снижали эффективность при выполнения такого запроса.

Если взглянуть на план выполнения, то в этом можно легко убедится:

2. UNPIVOT

С релизом SQL Server 2005, стало возможным использовать новую конструкцию языка T-SQL – UNPIVOT.

Применяя UNPIVOT предыдущий запрос можно упростить до:

SELECT UserID, BadgeCount, BadgeType
FROM dbo.UserBadges
UNPIVOT (
    BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
) unpvt

При выполнении мы получим следующий план:

3. VALUES

Начиная с SQL Server 2008 стало возможным использовать конструкцию VALUES не только для создания многострочных INSERT запросов, но и внутри блока FROM.

Применяя конструкцию VALUES, запрос выше можно переписать так:

SELECT p.UserID, t.*
FROM dbo.UserBadges p
CROSS APPLY (
    VALUES 
          (Gold,   'Gold')
        , (Silver, 'Silver')
        , (Bronze, 'Bronze')
) t(BadgeCount, BadgeType)

При этом, по-сравнению с UNPIVOT, план выполнения немного упростится:

4. Dynamic SQL

Применяя динамический SQL, есть возможность создать «универсальный» запрос для любой таблицы. Главное условие при этом — столбцы, которые не входят в состав первичного ключа, должны иметь совместимые между собой типы данных.

Узнать список таких столбцов можно следующим запросом:

SELECT c.name
FROM sys.columns c WITH(NOLOCK)
LEFT JOIN (
	SELECT i.[object_id], i.column_id
	FROM sys.index_columns i WITH(NOLOCK)
	WHERE i.index_id = 1
) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
WHERE c.[object_id] = OBJECT_ID('dbo.UserBadges', 'U')
	AND i.[object_id] IS NULL

Если посмотреть на план запроса, можно заметить, что соединение с sys.index_columns является достаточно затратной:

Чтобы избавится от этого соединения можно воспользоваться функцией INDEX_COL. В результате итоговый вариант запроса примет следующий вид:

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.UserBadges'

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT * 
FROM ' + @table_name + '
UNPIVOT (
    value FOR code IN (
        ' + STUFF((
    SELECT ', [' + c.name + ']'
    FROM sys.columns c WITH(NOLOCK)
    WHERE c.[object_id] = OBJECT_ID(@table_name)
				AND INDEX_COL(@table_name, 1, c.column_id) IS NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    )
) unpiv'

PRINT @SQL
EXEC sys.sp_executesql @SQL

При выполнении будет сформирован запрос в соответствии с шаблоном:

SELECT * 
FROM <table_name>
UNPIVOT (
	value FOR code IN (<unpivot_column>)
) unpiv

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

5. XML

Более элегантно реализовать динамический UNPIVOT возможно, если использовать следующий трюк с XML:

SELECT
      p.UserID
    , BadgeCount = t.c.value('.', 'INT') 
    , BadgeType = t.c.value('local-name(.)', 'VARCHAR(10)') 
FROM (
    SELECT 
          UserID
        , [XML] = (
                SELECT Gold, Silver, Bronze
                FOR XML RAW('t'), TYPE
            )
    FROM dbo.UserBadges
) p
CROSS APPLY p.[XML].nodes('t/@*') t(c)

В котором для каждой строки формируется XML вида:

<t Column1="Value1" Column2="Value2" Column3="Value3" ... />

После чего парсится имя каждого атрибута и его значения.

В большинстве случаев, при использовании XML получается более медленный план выполнения – это расплата за универсальность.

Теперь сравним полученные результаты:

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

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

Попробуем решить задачу применяя конструкцию UNPIVOT:

SELECT 
      UserID
    , GameType = (
        SELECT TOP 1 BadgeType 
        FROM dbo.UserBadges b2 
        UNPIVOT (
            BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
        ) unpvt
        WHERE UserID = b.UserID 
        ORDER BY BadgeCount DESC
    ) 
FROM dbo.UserBadges b

На плане выполнения видно, что проблема наблюдается в повторном чтении данных и сортировке, которая необходима для упорядочивания данных:

Избавится от повторного чтения достаточно легко, если вспомнить, что в подзапросе допускается использовать столбцы из внешнего блока:

SELECT 
	  UserID
	, GameType = (
		SELECT TOP 1 BadgeType
		FROM (SELECT t = 1) t 
		UNPIVOT (
			BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
		) unpvt
		ORDER BY BadgeCount DESC
	) 
FROM dbo.UserBadges

Повторные чтения ушли, но операция сортировки никуда не делась:

Посмотрим как ведет себя конструкция VALUES в данной задаче:

SELECT 
      UserID
    , GameType = (
            SELECT TOP 1 BadgeType
            FROM (
                VALUES
                      (Gold,   'Gold')
                    , (Silver, 'Silver')
                    , (Bronze, 'Bronze')
            ) t (BadgeCount, BadgeType)
            ORDER BY BadgeCount DESC
        ) 
FROM dbo.UserBadges

План ожидаемо упростился, но сортировка по-прежнему присутствует в плане:

Попробуем обойти сортировку используя аггрегирующую функцию:

SELECT 
	  UserID
	, BadgeType = (
            SELECT TOP 1 BadgeType
            FROM (
                VALUES
                      (Gold,   'Gold')
                    , (Silver, 'Silver')
                    , (Bronze, 'Bronze')
            ) t (BadgeCount, BadgeType)
			WHERE BadgeCount = (
				SELECT MAX(Value)
				FROM (
					VALUES (Gold), (Silver), (Bronze)
				) t(Value)
			)
		) 
FROM dbo.UserBadges

Мы избавились от сортировки:

Небольшие итоги:

В ситуации, когда необходимо произвести простое преобразование столбцов в строки, то наиболее предпочтительно использовать конструкции UNPIVOT или VALUES.

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

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

P.S. Чтобы адаптировать, часть примеров под особенности SQL Server 2005, конструкцию с применением VALUES:

SELECT * 
FROM (
	VALUES (1, 'a'), (2, 'b')
) t(id, value)

необходимо заменить на комбинацию SELECT UNION ALL SELECT:

SELECT id = 1, value = 'a'
UNION ALL
SELECT 2, 'b'

UPDATE 16/10/2013: Как ведут себя UNPIVOT и VALUES на больших объемах данных?

За основу взята таблица со следующей структурой (всего 25 столбцов).

CREATE TABLE [dbo].[WorkOutFactors]
(
	WorkOutID BIGINT NOT NULL PRIMARY KEY,
	NightHours INT NOT NULL,
	EveningHours INT NOT NULL,
	HolidayHours INT NOT NULL,
	...
)

Данная таблица содержит ~186000 строк. С холодного старта на локальном SQL Server 2012 SP1, операция преобразования строк в столбцы дала следующие результаты.

План выполнения UNPIVOT:

План выполнения VALUES:

В сравнении видно, что VALUES выполняется быстрее (на 3 секунды), но требует больших ресурсов CPU:

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

Pivot и Unpivot в SQL

В SQL Pivot и Unpivot — это реляционные операторы, которые используются для преобразования одной таблицы в другую, чтобы обеспечить более простое представление таблицы. Условно можно сказать, что оператор Pivot преобразует данные строк таблицы в данные столбцов. Оператор Unpivot делает наоборот: он преобразует данные на основе столбцов в строки.

Синтаксис:

1. Поворот:

SELECT (ColumnNames) 
FROM (TableName) 
PIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias) //Alias is a temporary name for a table

2. Отключить:

SELECT (ColumnNames) 
FROM (TableName) 
UNPIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias)

Пример-1:
Мы создали простую таблицу с именем «geeksforgeeks» со значениями, такими как название курса, категория курса и цена, и вставили соответствующие значения.

Create Table geeksforgeeks 
( 
CourseName nvarchar(50), 
CourseCategory nvarchar(50),
Price int  
) 

Insert into geeksforgeeks  values('C', 'PROGRAMMING', 5000) 
Insert into geeksforgeeks  values('JAVA', 'PROGRAMMING', 6000) 
Insert into geeksforgeeks  values('PYTHON', 'PROGRAMMING', 8000) 
Insert into geeksforgeeks  values('PLACEMENT 100', 'INTERVIEWPREPARATION', 5000) 

SELECT * FROM geeksforgeeks 

Результат, который мы получаем:

Теперь, применяя оператор PIVOT к этим данным:

SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION
FROM geeksforgeeks 
PIVOT 
( 
SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION ) 
) AS PivotTable 

После использования оператора Pivot мы получаем следующий результат:

Пример-2:
Теперь мы используем ту же таблицу «geeksforgeeks», созданную в приведенном выше примере, и применяем оператор Unpivot к нашей сводной таблице.

Применяя оператор UNPIVOT :

SELECT CourseName, CourseCategory, Price 
FROM 
(
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks 
PIVOT 
( 
SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION) 
) AS PivotTable
) P 
UNPIVOT 
( 
Price FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
) 
AS UnpivotTable

После использования оператора Unpivot мы возвращаем нашу исходную таблицу, поскольку мы успешно преобразовали столбцы таблицы обратно в строки:

Рекомендуемые посты:

Pivot и Unpivot в SQL

0.00 (0%) 0 votes

Универсальное динамическое транспонирование таблиц T-SQL (Dinamic PIVOT) — Статьи TechNet — Россия (Pусский)

• TechNet
• Продукты
• Ресурсы
• Скачать
• Обучение
• Поддержка

Продукты

• 
  Windows
• 
  Windows Server
• 
  System Center
• 
  Microsoft Edge

• 
  Office
• 
  Office 365
• 
  Exchange Server

• 
  SQL Server
• 
  Продукты SharePoint
• 
  Skype for Business
• 
  Просмотреть все продукты »

Resources

• 
  Channel 9 Video
• 
  Центр пробного ПО
• 
  Учебные материалы
• 
  Приложение Microsoft Tech
• 
  Учебные материалы
• 
  Microsoft Virtual Academy
• 
  Центр сценариев
• 
  Блоги по серверным продуктам и инструментам
• 
  Блог TechNet

• 
  Новостной бюллетень TechNet
• 
  Галерея TechNet
• 
  Библиотека TechNet
• 
  Видео
• 
  TechNet Wiki
• 
  Сайт Windows Sysinternals
• 
  Виртуальные лабораторные занятия

Solutions

• 
  Частное облако
• 
  Безопасность
• 
  Сети

Обновления

• 
  Пакеты обновления
• 
  Бюллетени по безопасности
• 
  Центр обновления Microsoft

Пробные версии

• 
  Windows Server 2016
• 
  System Center 2016
• 
  Windows 10 Enterprise
• 
  SQL Server 2016
• 
  Все ознакомительные версии »

Сайты по теме

• 
  Центр загрузки Microsoft
• 
  Microsoft Evaluation Center
• 
  Драйверы
• 
  Сайт Windows Sysinternals
• 
  Галерея TechNet

Обучение

• 
  Виртуальные занятия, проводимые экспертами
• 
  Каталог обучения
• 
  Система поиска курсов
• 
  Microsoft Virtual Academy
• 
  Бесплатные курсы по Windows Server
• 
  Курсы по Windows
• 
  SQL Server training
• 
  Microsoft Official Courses On-Demand

Сертификация

• 
  Обзор сертификаций
• 
  MCSA: Windows 10
• 
  MCSA: Windows Server
• 
  Сертификация по частному облаку
• 
  Сертификация по SQL Server

Другие материалы и ссылки

• 
  Мероприятия Microsoft
• 
  Подготовка к повторной сертификации
• 
  Блог Born To Learn

Продукты

• 
  Для бизнеса
• 
  Для разработчиков

sql — Unpivot с именем столбца

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

Обзор реляционных операторов Pivot и Unpivot статического и динамического SQL

В этой статье мы рассмотрим операторы SQL Pivot и SQL Unpivot и то, как они могут быть полезны для транспонирования данных SQL Server. Кроме того, мы обсудим как статические, так и динамические способы использования реляционных операторов PIVOT и UNPIVOT, которые можно использовать для преобразования агрегированных отдельных значений в виде столбцов в результирующем наборе путем указания всех значений столбцов в предложении PIVOT IN.

SQL Server pivot Введение

Есть несколько способов перенести набор данных из строк в столбцы и из столбцов в строки.SQL Pivot — это один из методов, который позволяет транспонировать строки в столбцы и параллельно выполняет возможные агрегаты. Операторы отношения SQL PIVOT и SQL UNPIVOT переносят двумерные данные с табличным значением в другую форму данных. SQL PIVOT переносит возвращающее табличное значение выражение из уникального набора значений из одного столбца в несколько столбцов в выходных данных и выполняет агрегирование. SQL UNPIVOT выполняет операцию, противоположную SQL PIVOT, путем преобразования столбцов возвращающего табличное значение выражения в значения столбцов.

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

Примечание. На приведенном выше изображении мы видим, что в процессе PIVOT значения столбца поворачиваются с вертикального на горизонтальный, а UNPIVOTING — это как поворот его с горизонтального на вертикальное.

Синтаксис

ВЫБЕРИТЕ <столбец без поворота>,

[pivot_column_1] AS <псевдоним столбца>,

[pivot_column_2] AS <псевдоним столбца>,

…

[pivot_column_n] AS <псевдоним столбца>

ОТ

(

<ВЫБРАТЬ ЗАПРОС>)

AS <Псевдоним для временного набора данных>

PIVOT

(

<Агрегатная функция> (Агрегатный столбец)

ЗА

[<Сводной столбец станет заголовком столбца>]

IN ([pivot_column_1], [pivot_column_2],

… [Pivot_column_n])

) AS <Псевдоним сводной таблицы>

<Предложение ORDER BY>;

Начало работы с SQL Pivot

Мы собираемся начать с поворота и отмены поворота данных.Теперь вы понимаете, что такое операторы PIVOT и UNPIVOT в SQL Server. Проще говоря, это просто еще один способ описания процесса превращения отдельных значений строк в их собственные столбцы, известный как Pivoting. А процесс превращения столбцов в строки известен как неповорот.

Пример 1 : Как выполнить операцию SQL Pivot

Представим себе образец набора данных, полученный из базы данных AdventureWorks2014. В этом случае вы получили простой набор данных со столбцом SalesYear и одним количественным значением TotalSales.

Теперь набор данных готов к повороту. У вас есть SalesYear в первом столбце, TotalSales во втором столбце. Если вы попросили транспонировать данные, вы должны взять каждый отдельный год в столбце, и эти столбцы станут заголовками сводных столбцов. Итак, 2011 становится первым столбцом, 2012 становится отдельным столбцом и так далее. Фактические значения из следующего столбца Totalsales, они соответствуют заголовкам сводных столбцов. Теперь сводная таблица будет выглядеть, как показано ниже.

На следующем изображении показаны входные данные, синтаксис Pivot, SQL Pivot и выходные результаты.

Пример 2 : Как выполнить операцию SQL Unpivot

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

В следующем примере набор данных с поворотом используется для отмены поворота значений.Ключевым моментом здесь является то, что это было легко сделать при преобразовании, потому что мы смогли создать сводную таблицу в качестве промежуточного шага, а затем выполнить преобразование с помощью операции unpivot.

Примечание. Операция поворота SQL приводит к переносу агрегированного результата в столбец, но в то время как этап отмены поворота SQL не является точным обращением транспонирования столбцов в отдельные значения строк. Оператор unpivot не разделяет агрегированные результаты.

Пример 3 : Как получить квартальные продажи с помощью операции сводной таблицы SQL

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

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

В следующем примере показано совокупное ежемесячное распределение продаж за год продаж.

Следующий вывод показывает базовые данные

Когда вы будете готовы с базовыми данными, вы можете применить оператор PIVOT. В следующем примере предложение IN принимает нечисловой символ, созданный на основе значения DATEPART. Значение DATEPART объединяется с «Q», а затем значения передаются в предложение IN оператора PIVOT.

Следующий результат представляет собой квартальную разбивку данных о продажах.

Пример 4 : Как получить ежемесячные продажи с помощью операции сводки SQL

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

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

Пример 5 : Как получить ежемесячные продажи с помощью операции динамической сводки SQL

Давайте рассмотрим ежемесячное разделение данных о продажах с помощью динамического поворота.До сих пор мы обсуждали статические операции поворота. Чтобы преобразовать статическую операцию поворота в динамическую, мы должны удалить жестко запрограммированные значения из предложения IN. Сначала получите отдельное выражение из набора данных, а затем подготовьте строку, объединив все выражения. В следующем примере @columnname используется для объединения всего выражения. Объединенное выражение передается в предложение pivot IN. Остальное — простое преобразование статического SQL в динамический SQL и вызов @DML с использованием хранимой процедуры sp_executesql.

На этом пока все …

Завершение

До сих пор мы продемонстрировали основные концепции и несколько примеров SQL PIVOT и SQL UNPIVOT.

Образец основан на базе данных SQL Server adventureworks2014. Вы можете сами попробовать и протестировать все образцы. Если у вас есть вопросы, не стесняйтесь оставлять комментарии ниже …

Я технолог по базам данных, имеющий более 11 лет богатого практического опыта работы с технологиями баз данных. Я сертифицированный специалист Microsoft и имею степень магистра компьютерных приложений.

Моя специальность заключается в разработке и внедрении решений высокой доступности и кроссплатформенной миграции БД.В настоящее время разрабатываются технологии SQL Server, PowerShell, Oracle и MongoDB.

Посмотреть все сообщения от Prashanth Jayaram

Последние сообщения от Prashanth Jayaram (посмотреть все)

.

Общие сведения об операторах PIVOT, UNPIVOT и Reverse PIVOT

Оператор PIVOT используется для преобразования строк таблицы в столбцы, а оператор UNPIVOT преобразовывает столбцы обратно в строки. Реверсирование оператора PIVOT относится к процессу применения оператора UNPIVOT к уже имеющемуся набору данных PIVOTED, чтобы получить исходный набор данных.

В этой статье мы рассмотрим эти три концепции на разных примерах.

Оператор PIVOT

Как упоминалось ранее, оператор PIVOT преобразует строки таблицы в столбцы.Например, если у вас есть таблица, которая выглядит так:

и разверните его по третьему столбцу, результат будет следующим:

В исходной таблице у нас было два уникальных значения для столбцов «Курс» — английский и история. В сводной таблице эти уникальные значения преобразованы в столбцы. Вы можете видеть, что значения баллов для каждого нового столбца остаются неизменными. Например, в исходной таблице студентка Салли набрала 95 баллов по английскому, в отличие от значений в сводной таблице.

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

Давайте посмотрим на этот пример использования оператора PIVOT в SQL Server.

 CREATE DATABASE School
ИДТИ

ЕГЭ школа
ИДТИ

СОЗДАТЬ ТАБЛИЦУ Студенты
(
Id INT ИДЕНТИФИКАЦИЯ ПЕРВИЧНОГО КЛЮЧА,
StudentName VARCHAR (50),
Курс VARCHAR (50),
Оценка INT
)
ИДТИ

ВСТАВИТЬ В ЦЕННОСТИ студентов ('Салли', 'английский', 95)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Салли», «История», 82)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Эдвард», «английский», 45)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Эдвард», «История», 78) 

Приведенный выше сценарий создает базу данных School.В базе данных мы создаем таблицу «Студенты» с четырьмя столбцами, такими как Id, StudentName, Course и Score. Наконец, мы добавляем четыре фиктивные записи в таблицу «Студенты».

Теперь, если вы используете оператор SELECT для получения всех записей, вы увидите следующее:

Давайте перевернем эту таблицу по столбцу «Курс». Для этого выполните следующий скрипт:

 ВЫБРАТЬ * ИЗ
 
(ВЫБРАТЬ
Имя студента,
Гол,
Курс
ОТ
Ученики
)
AS StudentTable
PIVOT (
СУММ (Оценка)
ДЛЯ курса IN ([английский], [история])
) AS SchoolPivot 

Давайте посмотрим, что происходит в сценарии.В первой строке мы используем оператор SELECT для определения столбцов, которые мы хотим добавить в сводную таблицу. Первые два столбца — это StudentName и Score. Данные для этих двух столбцов будут взяты непосредственно из таблицы «Студенты». Третий столбец — это курс. Мы хотим повернуть нашу таблицу по столбцу «Курс», поэтому столбец «Курс» будет разделен на количество столбцов, равное значениям, заданным оператором PIVOT для столбца «Курс».

Синтаксис оператора PIVOT прост.Во-первых, вы должны применить агрегатную функцию к столбцу, значения которого вы хотите отобразить в сводных столбцах. В нашем случае мы хотим отображать Score в поворотных столбцах — English и History. Наконец, мы используем оператор FOR, чтобы указать сводный столбец и уникальные значения в нем. Результат выглядит так:

Оператор UNPIVOT

Оператор UNPIVOT используется для преобразования столбцов таблицы в строки. Например, если у вас есть таблица, которая выглядит так:

Оператор UNPIVOT вернет следующие результаты:

Столбцы исходной таблицы преобразованы в строки несведенной таблицы.Давайте воспользуемся этими данными, чтобы увидеть, как оператор UNPIVOT работает в SQL.

Для этого выполните следующий скрипт:

 СОЗДАТЬ БАЗУ ДАННЫХ School2
ИДТИ

ЕГЭ Школа2
ИДТИ

СОЗДАТЬ ТАБЛИЦУ Студенты
(
Id INT ИДЕНТИФИКАЦИЯ ПЕРВИЧНОГО КЛЮЧА,
StudentName VARCHAR (50),
Математика INT,
Английский INT,
История INT,
Наука INT
)
ИДТИ

ВСТАВИТЬ В ЦЕННОСТИ студентов («Салли», 78, 85, 91, 76)
ВСТАВИТЬ ЦЕННОСТИ студентов («Эдвард», 87, 90, 82, 87) 

Если вы выберете данные из таблицы «Студенты» базы данных School2, вы увидите следующие результаты:

Чтобы применить к этой таблице оператор UNPIVOT, выполните следующий запрос:

 ВЫБЕРИТЕ имя студента, курс, результат
ОТ студентов
UNPIVOT
(
Гол
ДЛЯ Курс (математика, английский язык, история, естественные науки)
) AS SchoolUnpivot 

Синтаксис оператора UNPIVOT аналогичен синтаксису PIVOT.В операторе SELECT вам нужно указать столбцы, которые вы хотите добавить в выходную таблицу. В операторе UNPIVOT вы должны указать два столбца:

1. Первый столбец содержит значения из строк сводных столбцов (в данном случае это Score).
2. Второй столбец включает названия столбцов с поворотом, т.е. математика, английский язык, история и наука.

Выходная таблица будет выглядеть так:

Реверс PIVOT

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

Реверсивный неагрегатный поворотный стол

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

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

Как видите, повторяющиеся строки отсутствуют. Другими словами, мы можем сказать, что для каждого студента есть только одна запись на курс. Например, у Салли только один рекорд по количеству баллов по курсу английского языка.

Когда мы применили оператор PIVOT к приведенной выше таблице, мы получили следующий результат:

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

Примечание:

Мы можем выполнить этот запрос в базе данных School, которую мы создали в разделе оператора PIVOT.

 ВЫБЕРИТЕ имя студента, курс, результат
ОТ
(ВЫБРАТЬ ИЗ
 
(ВЫБРАТЬ
Имя студента,
Гол,
Курс
ОТ
Ученики
)
AS StudentTable
PIVOT (
СУММ (Оценка)
ДЛЯ курса IN ([английский], [история])
) AS SchoolPivot) Сводные результаты
UNPIVOT
(
Гол
ДЛЯ Курс (английский, история)
) AS Schoolunpivot 

Здесь мы используем подзапрос для применения оператора UNPIVOT к сводным данным.Внутренний запрос использует оператор PIVOT, а внешний запрос использует оператор UNPIVOT. На выходе вы увидите исходную таблицу «Студенты».

Реверсивный сводный стол

Ранее мы говорили, что отменить можно только оператор PIVOT, который не содержит агрегированных данных. Давайте попробуем перевернуть оператор PIVOT, содержащий агрегированные данные.

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

 ВСТАВИТЬ В ЦЕННОСТИ студентов («Эдвард», «История», 78) 

Теперь, если вы выберете все записи из таблицы «Студенты», вы получите следующий результат:

Мы видим, что у нас есть дубликат оценки Эдварда по истории.

Теперь примените к этой таблице оператор PIVOT.

 SELECT Id, StudentName, English, History
ОТ студентов
PIVOT
(
СУММ (Оценка)
ДЛЯ Курс (английский, история)
) AS Schoolpivot 

Из выходных данных видно, что функция СУММ в операторе PIVOT добавила два балла к курсу истории, взятому Эдвардом.Если вы попытаетесь перевернуть поворот этой таблицы (т.е. применить оператор UNPIVOT), вы не получите исходную таблицу. Он вернет четыре записи вместо исходных пяти. Столбец «История» для студента Эдварда будет содержать агрегированный результат, а не отдельные результаты.

Чтобы это увидеть, выполните следующий скрипт:

 ВЫБЕРИТЕ имя студента, курс, результат
ОТ
(ВЫБРАТЬ ИЗ
 
(ВЫБРАТЬ
Имя студента,
Гол,
Курс
ОТ
Ученики
)
AS StudentTable
PIVOT (
СУММ (Оценка)
ДЛЯ курса IN ([английский], [история])
) AS SchoolPivot) Сводные результаты
UNPIVOT
(
Гол
ДЛЯ Курс (английский, история)
) AS Schoolunpivot 

Выходная таблица будет выглядеть так:

Артикул:

Бен Ричардсон руководит организацией Acuity Training.Acuity — это компания, занимающаяся обучением в сфере ИТ, предлагающая аудиторные курсы в Лондоне и Гилфорде. Он является ведущим поставщиком обучения SQL в Великобритании и предлагает полный спектр обучения SQL от вводного обучения до курсов продвинутого администрирования. Последние сообщения Бена Ричардсона (посмотреть все).

MySQL, MariaDB, SQLite, PostgreSQL, Oracle,…

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

Это требование часто встречается в контексте отчетности. Следующее объяснение, таким образом, основано на запросе, который сообщает о ежемесячной выручке:

  ВЫБРАТЬ ВЫПИСКУ (ГОД ИЗ invoice_date) год
     , EXTRACT (МЕСЯЦ ИЗ invoice_date) месяц
     , SUM (доход) доход
  ИЗ счетов-фактур
 ГРУППА ПО ЭКСТРАКТУ (ГОД ОТ invoice_date)
        , EXTRACT (МЕСЯЦ ОТ invoice_date)  

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

Первым шагом в реализации этого требования является удаление месяца из группы на и , выбор предложений, чтобы получить одну строку в год:

  ВЫБРАТЬ ВЫДЕРЖКА (ГОД ИЗ invoice_date) год
     , SUM (доход) total_revenue
  ИЗ счетов-фактур
 ГРУППА ПО ВЫДЕРЖКАМ (ГОД ОТ invoice_date)  

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

Следующим шагом является определение двенадцати столбцов, в каждом из которых суммируются доходы только за один месяц. Например, чтобы получить доход за январь, выражение сумма (выручка) должно учитывать только счета за январь. Это можно легко сделать с помощью предложения filter :

  sum (выручка)  FILTER (WHERE EXTRACT (MONTH FROM invoice_date) = 1)   

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

Чтобы сделать запрос более понятным, выражение extract можно переместить в центральное место. Это может быть сгенерированный столбец или представление, чтобы другие запросы могли повторно использовать эти выражения. В этом примере достаточно централизовать выражение extract в запросе - либо используя с предложением , либо как встроенное представление:

  SELECT год
     , SUM (выручка) ФИЛЬТР (ГДЕ  месяц = ​​1 ) jan_revenue
     , SUM (выручка) ФИЛЬТР (ГДЕ  месяц = ​​2 ) feb_revenue
     ...
     , SUM (выручка) ФИЛЬТР (ГДЕ  месяц = ​​12 ) dec_revenue
  ОТ (ВЫБРАТЬ счета. *
 , ВЫПИСКА (ГОД ИЗ invoice_date) год
             , ВЫПИСКА (МЕСЯЦ ОТ invoice_date) месяц 
          ИЗ счетов-фактур
       ) счета-фактуры
 ГРУППА ПО году  

Соответствующие альтернативы

Несмотря на то, что предложение filter было введено в SQL: 2003, оно практически не поддерживается сегодня. К счастью, это не большая проблема, потому что case можно использовать для тех же целей.Уловка состоит в том, чтобы сопоставить значения, которые не соответствуют , а не критериям фильтрации, нейтральным значениям, которые не изменяют результат агрегирования. Null - очень хороший выбор для этого, потому что он не меняет результат любой агрегатной функции - даже avg . Кроме того, else null является предложением по умолчанию для case выражений без явного предложения else в любом случае - достаточно полностью пропустить предложение else .

  ВЫБРАТЬ год
     , SUM (CASE WHEN  month = 1  THEN выручка END) jan_revenue
     , SUM (CASE WHEN  month = 2  THEN выручка END) feb_revenue
       ...
     , SUM (CASE WHEN  month = 12  THEN выручка END) dec_revenue
  ОТ (ВЫБРАТЬ счета. *
 , ВЫПИСКА (ГОД ИЗ invoice_date) год
             , ВЫПИСКА (МЕСЯЦ ОТ invoice_date) месяц 
          ИЗ счетов-фактур
       ) счета-фактуры
 ГРУППА ПО году  

Выражение CASE WHEN month = 1 THEN выручка END оценивается как выручка для счетов-фактур с января.Для других счетов-фактур подразумеваемое значение else null возвращает значение null , которое не меняет результат суммы . См. Также « Null в агрегатных функциях (число, сумма,…) » и «Соответствующие альтернативы фильтру ».

Особый случай EAV

Самая большая проблема с проблемой поворота - это распознать ее, когда вы с ней столкнетесь. Это верно при работе с так называемой моделью значения атрибута объекта (EAV): это не похоже на проблему поворота, но, тем не менее, ее можно решить таким же образом.

Модель EAV доводит до крайности нормализацию и больше не использует столбцы традиционным способом. Вместо этого каждое отдельное значение хранится в отдельной строке. Помимо значения, в строке также есть столбец, в котором указывается, какой атрибут представляет значение, и третий столбец, в котором указывается, к какому объекту принадлежат значения. В конечном счете, таблица с тремя столбцами может содержать любые данные без необходимости изменять определение таблицы. Таким образом, модель EAV часто используется для хранения динамических атрибутов.

Модель EAV не лишена недостатков: например, практически невозможно использовать ограничения для проверки данных.Однако самая загадочная проблема с моделью EAV заключается в том, что преобразование в нотацию с одним столбцом на атрибут почти всегда выполняется с использованием объединений - довольно часто одно внешнее соединение на атрибут. Это не только громоздко, но и приводит к очень плохой производительности - настоящий анти-шаблон.

Однако превращение строк в столбцы является основной проблемой в чистом виде. Следовательно, эти шаги следует повторить снова: (1) используйте group by , чтобы уменьшить количество строк до одной строки на объект, (2) используйте filter или case , чтобы выбрать правильный атрибут для каждого столбца.

  ВЫБРАТЬ submission_id
     , MAX (CASE WHEN attribute = 'name' THEN value END) имя
     , MAX (CASE WHEN attribute = 'email' THEN value END) электронная почта
     , MAX (CASE WHEN attribute = 'website' THEN value END) веб-сайт
  ОТ form_submissions
 GROUP BY submission_id  

Обратите внимание на использование функции max : требуется уменьшить строки группы (все атрибуты) до одного значения. Это чисто синтаксическое требование, применимое независимо от фактического количества сгруппированных строк.

Чтобы получить исходное значение для каждого атрибута - даже если мы должны использовать агрегатную функцию - соответствующая логика фильтра ( случай или фильтр ) не должна возвращать более одного не нулевого значения . В приведенном выше примере очень важно, чтобы каждый из названных атрибутов ( name , email , website ) существовал только один раз на submission_id . Если дубликаты существуют, запрос возвращает только один из них.

Предварительное условие, согласно которому каждый атрибут не должен появляться более одного раза, лучше всего обеспечивается ограничением уникальности.В качестве альтернативы запрос может подсчитывать агрегированные строки с использованием count (*) и соответствующих case выражений (или filter clauses). Результаты могут быть проверены в приложении - если выбрано в качестве дополнительных столбцов - или в с предложением : с фильтром count (*) (...) <= 1 .

Если предварительное условие выполнено и агрегирование всегда выполняется для одного значения , не являющегося нулем, каждая агрегатная функция просто возвращает входное значение.Однако, min и max имеют то преимущество, что они также работают для символьных строк ( char , varchar и т. Д.).

Ограничения

SQL - это язык со статической типизацией: в запросе должны быть указаны столбцы результатов заранее. Чтобы сводить таблицу с неизвестными или динамическими атрибутами, можно использовать мультимножества или типы документов (XML, JSON) для представления хранилища ключей и значений в одном столбце. См. Соответствующие альтернативы listagg : типы документов.

Совместимость

Собственные альтернативы

pivot и unpivot (SQL Server, Oracle)

SQL Server поддерживает пункты pivot и unpivot с версии 2005 (документация). Они также доступны в базе данных Oracle, начиная с версии 11 g (документация).

Предложение модели (Oracle)

Проприетарное предложение модели , которое было введено с Oracle версии 10 g , также можно использовать для решения проблемы поворота (документация).

Таблица функций кросс-таблицы (PostgreSQL)

База данных PostgreSQL поставляется с набором табличных функций кросс-таблицы для сводных данных (документация).

.