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

Смена таблицы в SQL Server

В этой статье описывается, как повернуть таблицу в SQL Server.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 175574

Сводка

В этой статье описывается, как повернуть таблицу SQL Server. Предположим, что у вас есть таблица с именем QTRSALES . Таблица содержит столбцы YEAR , QUARTER и AMOUNT с данными в следующем формате.

За четвертый квартал 1996 года нет строки:

Год	Квартал	Amount
1995	1	125,000.90
1995	2	136,000.75
1995	3	212,000.34
1995	4	328,000.82
1996	3	728,000.35
1996	2	422,000.13
1996	1	328,000.82

Теперь предположим, что вы хотите повернуть таблицу, чтобы увидеть данные в следующем формате:

YEAR	Q1	В 2-й квартал	В3	Вопрос 4
1995	125,000.90	136,000.75	212,000.34	328,000.82
1996	328,000.82	422,000.13	728,000.35	0.00

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

Пример запроса для смены таблицы

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

SELECT YEAR, Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR = Q.YEAR),0), Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR = Q.YEAR),0), Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR = Q.YEAR),0), Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR = Q.YEAR),0) FROM QTRSALES Q GROUP BY YEAR 

Запрос больших таблиц

Для больших таблиц этот запрос будет выполняться быстрее:

SELECT YEAR, SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) AS Q1, SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) AS Q2, SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) AS Q3, SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) AS Q4 FROM qtrsales q GROUP BY YEAR 

Как транспонировать результаты sql-запроса

А вопрос заключается в том как этот результат повернуть, либо как написать правильный запрос. Ковырялся с UNION и с PIVOT не получилось.

Отслеживать
задан 31 июл 2015 в 9:45
659 1 1 золотой знак 7 7 серебряных знаков 20 20 бронзовых знаков
Подозреваю, что это гораздо труднее, чем кажется с первым взглядом.
31 июл 2015 в 9:50
используете mysql?
31 июл 2015 в 9:57

Тестовое задание на позицию Junior Developer. Видимо подразумевается независимость от субд. Остальные задачи были простыми. Я использую Firebird и MS.

31 июл 2015 в 9:57
31 июл 2015 в 10:17
Сейчас попробую
31 июл 2015 в 10:26

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

Используйте CASE или PIVOT. Здесь, фактически, ваш случай.

Отслеживать
ответ дан 31 июл 2015 в 11:02
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков

при помощи интернета и какой-то. получилось такое. Это только для MySQL, как я понимаю, из-за group_concat. Но, может, чем поможет

drop table if exists t1; create table t1 (id int, value char(1)); insert into t1 values (2, 'a'), (3, 'a'), (4, 'b'), (5, 'c'); select group_concat(if(v='a', c, null)) a, group_concat(if(v='b', c, null)) b, group_concat(if(v='c', c, null)) c from (select value v, Count(value) c from t1 group by value ) temp a b c 2 1 1 

Отслеживать
ответ дан 31 июл 2015 в 10:41
16.4k 2 2 золотых знака 15 15 серебряных знаков 24 24 бронзовых знака
да, group_concat в ms sql нету. Читал про него когда гуглил решение.
31 июл 2015 в 10:55

Не претендую на изящность решения, но вот вариант с курсором:

DECLARE @T2 table (id int, value char(1)) INSERT INTO @T2 values (2, 'a'), (3, 'a'), (4, 'b'), (5, 'c') DECLARE @vals varchar(10) DECLARE @cnts varchar(10) DECLARE @v char(1) DECLARE @c int DECLARE @cur cursor SET @cur = cursor local for SELECT value, COUNT(value) FROM @T2 GROUP BY value OPEN @cur FETCH NEXT FROM @cur INTO @v, @c WHILE @@FETCH_STATUS = 0 BEGIN IF @vals IS NULL SET @vals = @v ELSE SET @vals = @vals + ' ' + @v IF @cnts IS NULL SET @cnts = CAST(@c as varchar(10)) ELSE SET @cnts = @cnts + ' ' + CAST(@c as varchar(10)) FETCH NEXT FROM @cur INTO @v, @c END CLOSE @cur DEALLOCATE @cur -- ну и собственно результат: SELECT @vals SELECT @cnts 

Оператор PIVOT

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

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

Консоль

Выполнить

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

Консоль

Выполнить

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

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

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

Консоль

Выполнить

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

Консоль

Выполнить

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

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

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

Транспонирование таблицы SQL: какой запрос может в этом помочь

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

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

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

Транспонирование таблицы SQL

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

Дормидонт	Платон	Тимати	Аристарх
Красный	3	7	3	5
Зеленый	10	6	5	7
Голубой	4	4	11	3

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

Красный	Зеленый	Голубой
Дормидонт	3	10	4
Платон	7	6	4
Тимати	3	5	11
Аристарх	5	7	3

Когда вы создаете такую таблицу в SQL, код будет следующий:

CREATE TABLE transTable([color] varchar(5), [Дормидонт] int, [Платон] int, [Тимати] int, [Аристарх] int);

INSERT INTO transTable

([color], [Дормидонт], [Платон], [Тимати], [Аристарх])

VALUES

(‘Красный’, 3, 7, 3, 5),

(‘Зеленый’, 10, 6, 5, 7),

(‘Голубой’, 4, 4, 11, 3);

Транспонируем нашу SQL-таблицу с помощью специальной конструкции с применением операторов «pivot» и «unpiv o t». Код будет следующим:

select name, [Красный], [Зеленый], [Голубой]

from

(

select color, name, value

from transtable

unpivot

(

value for name in (Дормидонт, Платон, Тимати, Аристарх)

) unpiv

) src

pivot

(

sum(value)

for color in ([Красный], [Зеленый], [Голубой])

) piv

Заключение

Транспонирование таблицы SQL требуется достаточно редко. Транспонирование можно описать как процесс перевода столбцов в строки. Сегодня мы показали простейший, но эффективный способ, как транспонировать SQL-таблицу при помощи специальных операторов «pivot» и «unpivot».

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.