Как транспонировать в 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-запроса
А вопрос заключается в том как этот результат повернуть, либо как написать правильный запрос. Ковырялся с 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 Отслеживать
ответ дан 31 июл 2015 в 11:10
nezorflame nezorflame
11 1 1 бронзовый знак
- sql
- запрос
-
Важное на Мете
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.3.8.5973
Смена таблицы в 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
Для транспонирования строк в столбцы примените 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, применяя агрегирующую функцию для получения единой строки на группу.