Как перевернуть таблицу в sql

CROSSTAB в PostgreSQL

Повернуть таблицу в PostgreSQL можно при помощи функции CROSSTAB . Эта функция принимает в качестве текстового параметра SQL-запрос, который возвращает 3 столбца:

• идентификатор строки — т.е. этот столбец содержит значения, определяющие результирующую (повернутую) строку;
• категорию — уникальные значения из этого столбца образуют столбцы повернутой таблицы. Нужно отметить, что в отличие от PIVOT сами значения роли не играют; важно лишь их количество, которое определяет максимально допустимое количество столбцов;
• значение категории — собственно значения категорий. Размещение значений по столбцам производится слева направо, и имена категорий роли не играют, а только их порядок, определяемый сортировкой запроса.

Поясним сказанное на примере базы данных «Окраска».

Давайте для каждого квадрата просуммируем количество краски каждого цвета:

Консоль

Выполнить

Здесь мы ограничились только квадратами с номерами в диапазоне 12-16, чтобы, с одной стороны, уменьшить вывод, а, с другой стороны, сделать вывод презентативным. Сортировка по цветам выполнена в порядке RGB. Вот результат:

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

Теперь с помощью CROSSTAB попытаемся написать запрос, который бы дал требуемый результат:

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

Этот результат не вполне совпадает с ожидаемым. Напомним, что здесь важен только порядок. Если квадрат окрашивался только одним цветом, то значение (суммарный объем краски) попадет в первую категорию (у нас она называется R), каким бы этот единственный цвет ни был. Давайте перепишем запрос таким образом, чтобы он давал значения для всех цветов причем в нужном порядке. При этом отсутствующий цвет будем заменять NULL-значением. Чтобы добиться этого, добавим для каждого квадрата по одной строке каждого цвета со значением объема краски равным NULL:

Консоль

Выполнить

Оператор PIVOT

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

Задачу можно решить стандартными средствами с использованием оператора CASE:

Консоль

Выполнить

Теперь решение через PIVOT:

Консоль

Выполнить

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

Посчитать среднюю цену на ноутбуки в зависимости от размера экрана.

Задача элементарная и решается с помощью группировки:

Консоль

Выполнить

А вот как можно повернуть эту таблицу с помощью PIVOT:

Консоль

Выполнить

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

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

Я написал этот опус в помощь тем, кому оператор PIVOT интуитивно непонятен. Могу согласиться с тем, что в реляционном языке Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных.

Как транспонировать в sql

Для транспонирования таблицы в SQL необходимо использовать оператор PIVOT или функцию MAX или CASE в сочетании с оператором GROUP BY .

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

Синтаксис оператора PIVOT выглядит следующим образом:

SELECT * FROM table_name PIVOT ( aggregate_function(column_to_aggregate) FOR column_to_pivot IN (list_of_pivot_values) ) AS alias_name; 

Здесь table_name — это имя таблицы, которую нужно транспонировать aggregate_function — это агрегатная функция, которую нужно применить к столбцу, column_to_aggregate — это имя столбца, который нужно агрегировать, column_to_pivot — это имя столбца, который нужно использовать для создания новых столбцов, list_of_pivot_values — это список значений столбца column_to_pivot, для которых нужно создать новые столбцы, alias_name — это имя для результирующей таблицы

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

SELECT * FROM ( SELECT product_id, year, sales FROM sales_table ) AS source_table PIVOT ( SUM(sales) FOR year IN (2018, 2019, 2020) ) AS pivot_table; 

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

Если оператор PIVOT недоступен в вашей версии SQL, вы можете использовать функцию MAX или CASE в сочетании с оператором GROUP BY .

Синтаксис функции MAX для транспонирования таблицы выглядит следующим образом:

SELECT column_to_group_by, MAX(CASE column_to_pivot WHEN pivot_value_1 THEN value_to_show ELSE NULL END) AS pivot_value_1, MAX(CASE column_to_pivot WHEN pivot_value_2 THEN value_to_show ELSE NULL END) AS pivot_value_2, . FROM table_name GROUP BY column_to_group_by; 

Здесь column_to_group_by — это имя столбца, по которому нужно группировать данные, column_to_pivot — это имя столбца, который нужно использовать для создания новых столбцов, pivot_value_1 , pivot_value_2 — это значения столбца column_to_pivot , для которых нужно создать новые столбцы, value_to_show — это значение, которое нужно показать в новом столбце

Можно ли «развернуть» таблицу sql?

PropName может быть около 50, так что прописывать их вручную муторно.
Может добавить все в массив и выбирать каждое значение из него и создавать столбец? а затем наполнять его значениями?

• Вопрос задан более трёх лет назад
• 5838 просмотров

Комментировать
Решения вопроса 0
Ответы на вопрос 4
mrstrictly @mrstrictly
technet.microsoft.com/ru-ru/library/ms177410(v=sql.105).aspx
Ответ написан более трёх лет назад
Нравится 2 2 комментария

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

Николай Бронский @nbronskiy Автор вопроса

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

Простого решения нет, но скрипт должен быть не тяжелым, т.е берете из таблицы все уникальные ProductID, по каждому выбираете из таблицы строки, создаете массив где key это PropName, а value PropVal, потом записываете все в новую таблицу, где из массива создаете столбцы с именем key, и значением value, я бы записывал все в MongoDB

Ответ написан более трёх лет назад
Комментировать
Нравится 1 Комментировать

— с одной стороны ваша желаемая структра ближе к документно ориентировано БД чем реляционке (может с монгой какой поэксперементировать),
— с другой стороны если это psql то можно использовать json field (версия 9.2, в 9.3 расширили эту функционаьность)
— с третье стороны никто не мешает сразу писать в нужную структуру создав таблицу с 50-ю полями

А простого и быстрого способа вот так взять и развернуть я не знаю. Сам с у довольствием послушаю ответ если он есть.

Ответ написан более трёх лет назад
Николай Бронский @nbronskiy Автор вопроса

переношу данные с битрикса на kentico. И там и там MS SQL.
есть неплохой способ, но я в силу ограниченности знаний пока не очень в нем разобрался, но он работает — это я проверил:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(TEST_NAME)
from yourtable
FOR XML PATH(»), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,»)

set @query = ‘SELECT sbno,’ + @cols + ‘
from
(
select test_name, sbno, val
from yourtable
) x
pivot
(
max(val)
for test_name in (‘ + @cols + ‘)
) p ‘

Николай Турнавиотов @foxmuldercp
Системный администратор, программист, фотограф
может посмотрите в сторону группировки таки..
Ответ написан более трёх лет назад
Комментировать
Нравится Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

• SQL
• +1 ещё

Массив структур в Hive. Как проверить вхождение в массив структуры по маске?

• 1 подписчик
• вчера
• 72 просмотра