Как транспонировать таблицу в sql
Перейти к содержимому

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

  • автор:

Простой способ транспонирования строк и столбцов в SQL

Для транспонирования строк в столбцы примените SQL CASE в агрегатных функциях:

Скопировать код

SELECT MAX(CASE WHEN col = 'A' THEN value END) AS A, MAX(CASE WHEN col = 'B' THEN value END) AS B, MAX(CASE WHEN col = 'C' THEN value END) AS C FROM таблица GROUP BY grouping_col;

Альтернативно воспользуйтесь SQL Server PIVOT, чтобы запрос был проще:

Скопировать код

SELECT 'A', 'B', 'C' FROM таблица PIVOT (MAX(value) FOR col IN ('A', 'B', 'C')) AS PivotTable;

Замените col , value , таблица , grouping_col на реальные обозначения в вашей базе данных.

Основы транспонирования: глубже в тему

Динамическое транспонирование таблиц в SQL

При переменном числе столбцов следует применять Динамический SQL. Готовьте запрос в виде строки и выполняйте его, получая метаданные через information schema views.

Пример такого запроса:

Скопировать код

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); -- Получим названия столбцов для PIVOT SELECT @columns = STRING_AGG(QUOTENAME(column_name), ', ') FROM information_schema.columns WHERE table_name = 'YourTableName' AND data_type IN ('desired', 'data', 'types'); -- Составляем запрос SET @sql = N' SELECT * FROM YourTableName PIVOT ( MAX(value) FOR column_name IN (' + @columns + ') ) AS PivotTable;'; EXEC sp_executesql @sql;

Комбинация группировки и конкатенации строк

Для переноса определённых строк в столбцы опробуйте STRING_AGG или GROUP_CONCAT, применяя агрегирующую функцию для получения единой строки на группу.

Как транспонировать результаты 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.5k 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 

Отслеживать
ответ дан 31 июл 2015 в 11:10
nezorflame nezorflame
11 1 1 бронзовый знак

  • sql
  • запрос
    Важное на Мете
Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.4.30.8466

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

Lorem ipsum dolor

Транспонирование таблицы 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».

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

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

Как транспонировать несколько столбцов в несколько строк?

Сложно сформулировать, покажу на примере.
В таблице несколько полей с датами и им соответствуют поля со значениями, но мне не нужно, чтобы для каждого типа значений(val1, val2 и тд) были отдельные столбцы в выгрузке, мне нужно, чтобы был один столбец с названием значения и один со значениями, поэтому, я написал запрос:

select val1_date as date, 'val1' as val_name, count(val1) as value from . group by 1,2 union select val2_date as date, 'val2' as val_name, count(val2) as value from . group by 1,2

5dea09e67da2c961873816.png

Получаем соответственно:

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

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

4 комментария

Простой 4 комментария

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *