Pivot t sql: Использование операторов PIVOT и UNPIVOT — SQL Server
Содержание
sql server — PIVOT в MS SQL использовать без агрегатной функции
Вопрос задан
Изменён
5 лет 4 месяца назад
Просмотрен
855 раз
Есть таблица, содержит следующие данные :
Eсть еще один столбец id он уникальный, но он не участвует в select.
Мне необходимо получить результат в одну строку, где первый столбец shiftcode все остальные это от 0 до 5 со значениями которые указаны в столбце arrivedTime. Я пробую сделать так :
SELECT shiftcode, [0], [1], [2] FROM newRegistr PIVOT( max(arrivedTime) FOR newRegistr.sort IN ([0], [1], [2]) )AS test WHERE shiftCode = '4168428'
Получаю :
Что можно сделать? Цель — не использовать 6 под запросов.
- sql
- sql-server
11
Делайте PIVOT только на основе тех данных, которые участвуют в целевой выборке:
SELECT shiftcode, [0], [1], [2] FROM
(
SELECT shiftcode, sort, arrivedTime FROM newRegistr
WHERE shiftCode = '4168428' -- можно переставить ниже
) as rec
PIVOT( max(arrivedTime)
FOR rec.sort
IN ([0], [1], [2])
)AS test
или через CTE:
;with RecordsToPivot as
(
SELECT shiftcode, sort, arrivedTime FROM newRegistr
WHERE shiftCode = '4168428' -- можно переставить ниже
)
SELECT shiftcode, [0], [1], [2] FROM RecordsToPivot
PIVOT( max(arrivedTime)
FOR RecordsToPivot.sort
IN ([0], [1], [2])
)AS test
0
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
sql — Перевернуть таблицу (Pivot)
Задать вопрос
Вопрос задан
Изменён
1 месяц назад
Просмотрен
856 раз
Есть простая таблица:
[name] [value] цвет синий размер 35 форма куб
Как повернуть таблицу, чтобы получилось так:
[цвет] [размер] [форма] синий 35 куб
UPD
PIVOT конечно классная штука, на одном наборе данных работает, но как быть если наборов данных несколько ?
например:
[name] [value] цвет синий размер 35 форма куб цвет красный размер 10 форма шар
проблема в том что PIVOT требует чтобы была агрегирующая функция, к примеру если использовать MIN(), то на выходе будет:
[цвет] [размер] [форма] красный 10 куб
а должно быть 2 строки:
[цвет] [размер] [форма] синий 35 куб красный 10 шар
- sql
- sql-server
1
Попробуйте использовать оператор Pivot.
Вот пример использования взятый отсюда:
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Обычный select:
DaysToManufacture AverageCost 0 5.0885 1 223.88 2 359.1082 4 949.4105
С Pivot:
Cost_Sorted_By_Production_Days 0 1 2 3 4 AverageCost 5.0885 223.88 359.1082 NULL 949.4105
4
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
сводные данные в SQL | Расширенный SQL
Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных.
Проверьте начало.
В этом уроке мы рассмотрим:
- Сведение строк в столбцы
- Сведение столбцов к строкам
- Что дальше?
Сводка строк в столбцы
В этом уроке вы научитесь брать данные, отформатированные для анализа, и сворачивать их для презентации или построения диаграмм. Мы возьмем набор данных, который выглядит так:
И сделать так:
В этом примере мы будем использовать тот же набор данных об игроках College Football, что и на уроке CASE. Вы можете просмотреть данные прямо здесь.
Начнем с агрегирования данных, чтобы показать количество игроков за каждый год в каждой конференции, аналогично первому примеру урока внутреннего соединения:
SELECT team.conference AS конференция
игроки.год,
COUNT(1) КАК игроков
ОТ игроков benn.college_football_players
ПРИСОЕДИНЯЙТЕСЬ к командам benn.college_football_teams
ON team.school_name = игроки.school_name
СГРУППИРОВАТЬ НА 1,2
ЗАКАЗАТЬ ПО 1,2
Посмотреть в режиме.
Чтобы преобразовать данные, нам нужно поместить вышеуказанный запрос в подзапрос. Может быть полезно создать подзапрос и выбрать из него все столбцы, прежде чем начинать выполнять преобразования. Повторное выполнение запроса с такими поэтапными шагами упрощает отладку, если ваш запрос не выполняется. Обратите внимание, что вы можете удалить предложение ORDER BY из подзапроса, поскольку мы изменим порядок результатов во внешнем запросе.
ВЫБЕРИТЕ *
ОТ (
ВЫБЕРИТЕ team.conference КАК конференцию,
игроки.год,
COUNT(1) КАК игроков
ОТ игроков benn.college_football_players
ПРИСОЕДИНЯЙТЕСЬ к командам benn.college_football_teams
ON team.school_name = игроки.school_name
СГРУППИРОВАТЬ НА 1,2
) суб
Предполагая, что все работает, как и планировалось (результаты должны выглядеть точно так же, как и при первом запросе), пришло время разбить результаты на разные столбцы за разные годы.
Каждый элемент в операторе SELECT создает столбец, поэтому вам придется создавать отдельный столбец для каждого года:
SELECT конференция,
SUM(CASE WHEN year = 'FR' THEN player ELSE NULL END) КАК fr,
SUM(CASE WHEN year = 'SO' THEN player ELSE NULL END) ТАК,
СУММА(СЛУЧАЙ, КОГДА год = 'JR', ТОГДА игроки, ИНАЧЕ NULL END) КАК младший,
СУММА(СЛУЧАЙ, КОГДА год = 'SR', ТОГДА игроки, ИНАЧЕ NULL END) КАК sr
ОТ (
ВЫБЕРИТЕ team.conference КАК конференцию,
игроки.год,
COUNT(1) КАК игроков
ОТ игроков benn.college_football_players
ПРИСОЕДИНЯЙТЕСЬ к командам benn.college_football_teams
ON team.school_name = игроки.school_name
СГРУППИРОВАТЬ НА 1,2
) суб
СГРУППИРОВАТЬ ПО 1
ЗАКАЗАТЬ ПО 1
Технически, вы достигли цели этого урока. Но это все еще можно сделать немного лучше. Вы заметите, что приведенный выше запрос создает список, упорядоченный по конференции в алфавитном порядке.
Возможно, имеет смысл добавить столбец «Всего игроков» и упорядочить его (от большего к меньшему):
SELECT конференция,
SUM(игроков) КАК total_players,
SUM(CASE WHEN year = 'FR' THEN player ELSE NULL END) КАК fr,
SUM(CASE WHEN year = 'SO' THEN player ELSE NULL END) ТАК,
СУММА(СЛУЧАЙ, КОГДА год = 'JR', ТОГДА игроки, ИНАЧЕ NULL END) КАК младший,
СУММА(СЛУЧАЙ, КОГДА год = 'SR', ТОГДА игроки, ИНАЧЕ NULL END) КАК sr
ОТ (
ВЫБЕРИТЕ team.conference КАК конференцию,
игроки.год,
COUNT(1) КАК игроков
ОТ игроков benn.college_football_players
ПРИСОЕДИНЯЙТЕСЬ к командам benn.college_football_teams
ON team.school_name = игроки.school_name
СГРУППИРОВАТЬ НА 1,2
) суб
СГРУППИРОВАТЬ ПО 1
ЗАКАЗАТЬ ПО 2 DESC
Готово! Просмотрите это в режиме.
Объединение столбцов в строки
Многие данные, которые вы найдете в Интернете, отформатированы для потребления, а не для анализа.
Возьмем, к примеру, эту таблицу, показывающую количество землетрясений по всему миру с 2000 по 2012 год:
.
В этом формате сложно ответить на такие вопросы, как «Какова средняя магнитуда землетрясения?» Было бы намного проще, если бы данные отображались в 3 колонки: «магнитуда», «год» и «количество землетрясений». Вот как преобразовать данные в эту форму:
Сначала проверьте эти данные в режиме:
SELECT * ИЗ tutorial.worldwide_earthquakes
Примечание: имена столбцов начинаются с ‘year_’, поскольку в соответствии с режимом имена столбцов должны начинаться с букв.
Первое, что нужно сделать, это создать таблицу, в которой перечислены все столбцы исходной таблицы в виде строк новой таблицы. Если у вас нет тонны столбцов для преобразования, самый простой способ часто — просто перечислить их в подзапросе:
SELECT year
ИЗ (ЗНАЧЕНИЙ (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007 г.), (2008 г.), (2009 г.
)),(2010),(2011),(2012)) v(год)
)),(2010),(2011),(2012)) v(год)
Получив это, вы можете перекрестно соединить его с таблицей world_earthquakes , чтобы создать расширенное представление:
SELECT years.*,
землетрясения.*
ИЗ tutorial.worldwide_earthquakes землетрясения
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ (
ВЫБЕРИТЕ год
ИЗ (ЗНАЧЕНИЙ (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(год)
) годы
Обратите внимание, что каждая строка в world_earthquakes повторяется 13 раз. Последнее, что нужно сделать, это исправить это с помощью оператора CASE , который извлекает данные из правильного столбца в таблице world_earthquakes с учетом значения в столбце year :
SELECT years.*,
землетрясения.величина,
ДЕЛО год
КОГДА 2000 ТО год_2000
КОГДА 2001 ТОГДА год_2001
КОГДА 2002 ТОГДА год_2002
КОГДА 2003 ТОГДА год_2003
КОГДА 2004 ТОГДА год_2004
КОГДА 2005 ТОГДА год_2005
КОГДА 2006 ТОГДА год_2006
КОГДА 2007 ТОГДА год_2007
КОГДА 2008 ТОГДА год_2008
КОГДА 2009ТОГДА год_2009
КОГДА 2010 ТОГДА год_2010
КОГДА 2011 ТОГДА год_2011
КОГДА 2012 ТОГДА год_2012
ИНАЧЕ НУЛЬ КОНЕЦ
AS количество_землетрясений
ИЗ tutorial. worldwide_earthquakes землетрясения
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ (
ВЫБЕРИТЕ год
ИЗ (ЗНАЧЕНИЙ (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(год)
) годы
worldwide_earthquakes землетрясения
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ (
ВЫБЕРИТЕ год
ИЗ (ЗНАЧЕНИЙ (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(год)
) годы
Просмотр конечного продукта в режиме.
Что дальше?
Поздравляем с окончанием курса Advanced SQL Tutorial! Теперь, когда вы разобрались с SQL, следующим шагом будет оттачивание вашего аналитического процесса.
Именно для этой цели мы создали раздел обучения SQL Analytics. С поддельными наборами данных для имитации реальных ситуаций вы можете подойти к этому разделу как к обучению на рабочем месте. Проверьте это! А если вам нужно еще немного попрактиковаться со сводными таблицами, ознакомьтесь с нашими 4 полезными советами по SQL.
SQL Server динамический запрос PIVOT?
Я знаю, что этот вопрос старше, но я просматривал ответы и подумал, что смогу расширить «динамическую» часть проблемы и, возможно, помочь кому-то.
В первую очередь я создал это решение, чтобы решить проблему, с которой столкнулись несколько сотрудников, связанные с непостоянными и большими наборами данных, которые необходимо быстро обрабатывать.
Это решение требует создания хранимой процедуры, поэтому, если это не подходит для ваших нужд, прекратите читать сейчас.
Эта процедура будет принимать ключевые переменные оператора сводки для динамического создания операторов сводки для различных таблиц, имен столбцов и агрегатов. Столбец Static используется как столбец group by/identity для сводной таблицы (это можно убрать из кода, если в этом нет необходимости, но это довольно распространено в операторах сводной таблицы и было необходимо для решения исходной проблемы). Конечные результирующие имена столбцов будут сгенерированы, а столбец значений — это то, к чему будет применяться агрегат. Параметр Table — это имя таблицы, включая схему (schema.tablename). К этой части кода можно отнестись с любовью, потому что она не так чиста, как хотелось бы.
Это сработало для меня, потому что мое использование не было публично раскрыто, и инъекция sql не вызывала беспокойства. Параметр Aggregate будет принимать любые стандартные SQL-агрегаты ‘AVG’, ‘SUM’, ‘MAX’ и т. д. Код также по умолчанию использует значение MAX, так как агрегирование в этом нет необходимости, но аудитория, для которой это было изначально создано, не понимала повороты и, как правило, используя max в качестве агрегата.
Давайте начнем с кода для создания хранимой процедуры. Этот код должен работать во всех версиях SSMS 2005 и выше, но я не тестировал его в 2005 или 2016, но не понимаю, почему он не работает.
создать ПРОЦЕДУРУ [dbo].[USP_DYNAMIC_PIVOT]
(
@STATIC_COLUMN VARCHAR(255),
@PIVOT_COLUMN VARCHAR(255),
@VALUE_COLUMN VARCHAR(255),
@ТАБЛИЦА VARCHAR(255),
@АГРЕГАТ VARCHAR(20) = ноль
)
КАК
НАЧИНАТЬ
УСТАНОВИТЬ БЕЗ СЧЕТА;
объявить @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
@SQLSTRING NVARCHAR(МАКС),
@PIVOT_SQL_STRING NVARCHAR(МАКС),
@TEMPVARCOLUMNS NVARCHAR(МАКС),
@TABLESQL NVARCHAR(МАКС)
если isnull(@AGGREGATE,'') = ''
начинать
УСТАНОВИТЬ @AGGREGATE = 'МАКС'
конец
SET @PIVOT_SQL_STRING = 'ВЫБЕРИТЕ первые 1 ВЕЩЬ((ВЫБЕРИТЕ отдельные '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']'' AS VARCHAR(50)) [ текст()]
ОТ '+@TABLE+'
ГДЕ ISNULL('+@PIVOT_COLUMN+','''') <> ''''
ДЛЯ XML ПУТЬ(''''), ТИП)
. value(''.'',''NVARCHAR(MAX)''),1,2,'' '') как PIVOT_VALUES
из '+@TABLE+' ма
ЗАКАЗАТЬ ПО ' + @PIVOT_COLUMN + ''
объявить @TAB КАК ТАБЛИЦА (COL NVARCHAR (MAX))
ВСТАВИТЬ В @TAB EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT
SET @AVAIABLE_TO_PIVOT = (ВЫБРАТЬ * ИЗ @TAB)
SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')
SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')
ВСТАВИТЬ В @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')
выберите из (
SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a
ВРАЩАТЬСЯ
(
'+@ОБЪЕДИНИТЬ+'('+@VALUE_COLUMN+')
FOR '+@PIVOT_COLUMN+' В ('+@AVAIABLE_TO_PIVOT+')
) пив
ВЫБЕРИТЕ * ОТ @RETURN_TABLE'
EXEC SP_EXECUTESQL @SQLSTRING
КОНЕЦ
value(''.'',''NVARCHAR(MAX)''),1,2,'' '') как PIVOT_VALUES
из '+@TABLE+' ма
ЗАКАЗАТЬ ПО ' + @PIVOT_COLUMN + ''
объявить @TAB КАК ТАБЛИЦА (COL NVARCHAR (MAX))
ВСТАВИТЬ В @TAB EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT
SET @AVAIABLE_TO_PIVOT = (ВЫБРАТЬ * ИЗ @TAB)
SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')
SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')
ВСТАВИТЬ В @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')
выберите из (
SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a
ВРАЩАТЬСЯ
(
'+@ОБЪЕДИНИТЬ+'('+@VALUE_COLUMN+')
FOR '+@PIVOT_COLUMN+' В ('+@AVAIABLE_TO_PIVOT+')
) пив
ВЫБЕРИТЕ * ОТ @RETURN_TABLE'
EXEC SP_EXECUTESQL @SQLSTRING
КОНЕЦ
Далее мы подготовим наши данные для примера.
Я взял пример данных из принятого ответа с добавлением нескольких элементов данных для использования в этом доказательстве концепции, чтобы показать различные результаты совокупного изменения.
создать временную таблицу
(
дата дата и время,
категория varchar(3),
сумма денег
)
вставить во временные значения («01.01.2012», «ABC», 1000,00)
вставить во временные значения ("1/1/2012", "ABC", 2000.00) -- добавлено
вставить во временные значения ("01.02.2012", "DEF", 500.00)
вставить во временные значения ('01.02.2012', 'DEF', 1500.00) -- добавлено
вставить во временные значения («01.02.2012», «GHI», 800,00)
вставить во временные значения («10.02.2012», «DEF», 700.00)
вставить во временные значения ('10.02.2012', 'DEF', 800.00) -- добавлено
вставить во временные значения ("01.03.2012", "ABC", 1100.00)
В следующих примерах показаны различные операторы выполнения, показывающие различные агрегаты в качестве простого примера. Я не решил изменить статические столбцы, сводные столбцы и столбцы значений, чтобы сделать пример простым.