You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.

Загрузка...

pivot sql пример - Transact-SQL, ms sql server t sql

  1. Ручное формирование PIVOT запроса;
  2. Динамическое формирование PIVOT запроса (derived table);
  3. Динамическое формирование PIVOT запроса (CTE);
  4. Создание процедуры;
  5. Вызов процедуры из Excel.

 

Данная иснструкция посвещена созданию сводной таблицы средствами языка Transact-SQL - Mircosoft SQL Server, а именно с помощью оператора PIVOT.

В работе будет использоваться база данных TSQLV6 Ицика Бен-Гана взятая с сайта https://itziktsql.com/r-downloads

Оператор PIVOT работает в контексте клаузулы FROM. В общем и целом он (PIVOT) выглядит как-то так:

SELECT  some_column, list_of_columns
FROM source_table
PIVOT( agg_function ( column_for_aggregation )
FOR spreading_element IN ( list_of_columns ))

Для запроса необходимы 3 колонки двух из них (some_column, spreading_element) содержат какие-либо названия, а в третьей (column_for_aggregation) содержатся данные , которые можно аггрегировать, т.е. использовать такие функции как: SUM - сумма, AVG - среднее  арифметическое и т.д.

some_column - колонка, данные которой будут расположены по вертикали вниз.

spreading_element - колонка, данные которой будут расположены (повёрнуты на 90 градусов вправо) по горизонтали, в данном случае после some_column.

list_of_columns - те самые данные/значения колонки spreading_elemen, которые станут названиями самостоятельных колонок.

agg_function - буду использовать SUM для демонстрации

source_table - таблица может быть как одиночной, так и соединением многих таблиц. На мой взгляд лучший вариант использовать одно из табличный выражений: derived table - производная таблица (SELECT в клаузуле FROM), CTE (common table expression) - обобщённое табличное выражение. Или использовать VIEW - она же вьюха.

Теперь подробнее, в качестве source_table буду использовать относительно сложный запрос к таблицам Sales.Orders и HR.Employees из базы данных TSQLV6:


USE TSQLV6;

SELECT firstname + ' ' + lastname AS fio
	, shipcountry
	, freight
FROM Sales.Orders so
	INNER JOIN HR.Employees he ON he.empid = so.empid

Результат ниже:

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

fio - сотрудники организации, будет использована в качестве some_column

shipcountry - названия стран, будет использованаа в качестве spreading_element, название каждой страны станет отдельной колонкой.

freight - скорее всего вес груза, данные из этой колонки будут использованы для аггрегации в column_for_aggregation, поэтом в списке SELECT её как бы и нет.

Приступим к самому интересному - формирование запроса. Для начала в ручном режиме (ручной режим - это самое ужасное!):

Из колонки shipcountry выписываем все страны как-то так: [Finland],[USA],[Italy],[Brazil],..... и размещаем его в 2 места - в SELECT и в блок IN:


USE TSQLV6;

SELECT fio, [Finland],[USA],[Italy],[Brazil],[Germany],[Switzerland],[Mexico],[Sweden],[Argentina],[Austria],[UK],[Poland],[Canada],[Ireland],[Norway],[France],[Belgium],[Spain],[Venezuela],[Denmark],[Portugal]
FROM
(
	SELECT firstname + ' ' + lastname AS fio
		, shipcountry
		, freight
	FROM Sales.Orders so
		INNER JOIN HR.Employees he ON he.empid = so.empid
) AS src
PIVOT (SUM(freight) FOR shipcountry IN([Finland],[USA],[Italy],[Brazil],[Germany],[Switzerland],[Mexico],[Sweden],[Argentina],[Austria],[UK],[Poland],[Canada],[Ireland],[Norway],[France],[Belgium],[Spain],[Venezuela],[Denmark],[Portugal]
)) AS d;

и выполняем:

В качестве результата была получена та самая сводная таблица средствами T-SQL

Самое время перейти к динамическому SQL. Будет создано 2 скрипта как с derived table так и с CTE.

 

Создадим переменную @country, в которую поместим сформированный список стран:


DECLARE @country NVARCHAR(MAX) = '';


SELECT @country += '[' + shipcountry + '],'
FROM (
	SELECT DISTINCT shipcountry
	FROM Sales.Orders
) AS p


PRINT @country;

Посмотрим результат:

Вроде бы всё должно устраивать и можно  пользоваться списком из переменной @country, но нужно избавиться от последнего символа, которым является запятая:

Задействуем 2 фунцкии для этих целей: LEN() и STUFF():


DECLARE @country NVARCHAR(MAX) = '';


SELECT @country += '[' + shipcountry + '],'
FROM (
	SELECT DISTINCT shipcountry
	FROM Sales.Orders
) AS p


PRINT @country;

SET @country = STUFF(@country, LEN(@country), 1, '');

PRINT @country;

В результате получаем:

Первый PRINT @country; выдаёт результат  с запятой на конце, второй - уже без запятой.

Формируем полный скрпит/запрос с использованием производной таблицы (derived table):


USE TSQLV6;

DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @country NVARCHAR(MAX) = '';

SELECT @country += '[' + shipcountry + '],'
FROM (
	SELECT DISTINCT shipcountry
	FROM Sales.Orders
) AS p

--PRINT @country;

SET @country = STUFF(@country, LEN(@country), 1, '');

--PRINT @country;

SET @sql += 'SELECT fio, ';
SET @sql += @country + CHAR(13) + CHAR(10);
SET @sql += 'FROM
(
	SELECT firstname + '' '' + lastname AS fio
		, shipcountry
		, freight
	FROM Sales.Orders so
		INNER JOIN HR.Employees he ON he.empid = so.empid
) AS src
PIVOT (SUM(freight) FOR shipcountry IN(';
SET @sql += @country + CHAR(13) + CHAR(10);
SET @sql += ')) AS d;';

--PRINT @sql;

EXEC (@sql);

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

 

 

Формируем запрос с помощью CTE:


USE TSQLV6;

DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @country NVARCHAR(MAX) = '';

SELECT @country += '[' + shipcountry + '], '
FROM (
	SELECT DISTINCT shipcountry
	FROM Sales.Orders
) AS p

--PRINT @country;

SET @country = STUFF(@country, LEN(@country), 1, '');

--PRINT @country;

SET @sql = 'WITH src AS 
(
	SELECT firstname + '' '' + lastname AS fio
		, shipcountry
		, freight
	FROM Sales.Orders so
		INNER JOIN HR.Employees he ON he.empid = so.empid
)';
SET @sql += 'SELECT fio, ';
SET @sql += @country + CHAR(13) + CHAR(10);
SET @sql += 'FROM src
PIVOT (SUM(freight) FOR shipcountry IN(';
SET @sql += @country + CHAR(13) + CHAR(10);
SET @sql += ')) AS d;';

--PRINT @sql;

EXEC (@sql);

В качестве результата получаем абсолютно такой же результат:

Казалось бы всё, но есть одно НО, которое никого не устроит - страны перечислены не в алфавитном порядке! И проблема здесь в том, что при использовании derived talbe или CTE невозможно использовать сортировку ORDER BY. Но решение есть! Нас выручит индекс:


USE TSQLV6;

CREATE INDEX IX_shipcountry
ON Sales.Orders (shipcountry);

Создаём индекс затем выделяем часть кода в SSMS и нажимаем сочетание клавишь CTRL+L и смотрим будет ли использоваться индекс:

Как видно из плана запроса созданный нами индекс IX_shipcountry используется.

И снова выполним любой из запросов:

Благодаря индексу получаем отсортированный список стран.

 

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


USE TSQLV6;
GO
CREATE PROC EmployeeCountry
AS
BEGIN

	DECLARE @sql NVARCHAR(MAX) = '';
	DECLARE @country NVARCHAR(MAX) = '';

	SELECT @country += '[' + shipcountry + '],'
	FROM (
		SELECT DISTINCT shipcountry
		FROM Sales.Orders
	) AS p

	--PRINT @country;

	SET @country = STUFF(@country, LEN(@country), 1, '');

	--PRINT @country;

	SET @sql += 'SELECT fio, ';
	SET @sql += @country + CHAR(13) + CHAR(10);
	SET @sql += 'FROM
	(
		SELECT firstname + '' '' + lastname AS fio
			, shipcountry
			, freight
		FROM Sales.Orders so
			INNER JOIN HR.Employees he ON he.empid = so.empid
	) AS src
	PIVOT (SUM(freight) FOR shipcountry IN(';
	SET @sql += @country + CHAR(13) + CHAR(10);
	SET @sql += ')) AS d;';

	--PRINT @sql;

	EXEC (@sql);

END;

Если вы создали процедуру, но затем что-то в ней нужно менять, то CREATE замените на ALTER .

Вызываем процедуру EmployeeCountry:


USE TSQLV6;

exec dbo.EmployeeCountry;

Обратите внимани на то, что при создании процедуры не было использовано имени схемы, поэтому процедура была помещена в схему по умолчанию - dbo. 

 

Процедуры удобны для их использования например в MS Excel. Открываем Excel.

Переходим на вкладку Данные->Получение внешних данных->Из других источников->С сервера SQL Server и кликаем мышкой:

Логинимся используя такие данные как имя сервера, логин пароль и т.д., т.к. эксперименты провожу на обычном ПК, то логинюсь используя проверку подлинности Windows, и жмём Далее:

Выбираем базу TSQLV6, убираем все галочки и жмём Далее:

Жмём Готово:

Все остальные окна, что всплывут после - закрываем.

И снова вкладка Данные->Получение внешних данных->Существующие подключения   - жмём мышкой:

Кликаем на созданное нами подключение и  кликаем на Открыть:

Жмём Ок:

Переходим в Свойства:

В Свойствах подключения переходим на вкладку Определение. В раскладке Тип команды выбираем SQL. В Текст команды записываем вызов процедуры exec EmployeeCountry; , ради эксперимента схему dbo убрал. Жмём ОК:

Если всплыло такое окно - жмём Да:

В импорте данных уже жмём Ок:

В результате получаем такой же результат как и в SSMS:

Для внешних источников лучше использовать всё же вызов процедур, т.к. в противном случае, в том месте в Excel где мы использовали вызов процедуры придётся использовать все скрип/запрос, а это не очень хорошо.

Так же хотел бы упомянуть, что страница в Excel вниз можен содержать не более чем 2 в 20-й степени строк = 1 048 576 строк, это не так уж и много, ежели делать сводную таблица средствами excel, то исходные данные могут просто не поместиться.

PS:  Если вы решили использовать вышепреведённый код, вы это делаете на свой страх и риск.

 

Предыдущая статья
pivot sql пример - Transact-SQL, ms sql server t sql