Простой способ транспонирования строк и столбцов в 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, применяя агрегирующую функцию для получения единой строки на группу.
SELECT grouping_col,
STRING_AGG(value, ', ') WITHIN GROUP (ORDER BY value) AS combined_column
FROM таблица
GROUP BY grouping_col;
Преобразование строк в столбцы с использованием Общих Табличных Выражений
Общие Табличные Выражения (CTE) базово упрощают операции транспонирования, позволяя манипулировать промежуточными результатами.
WITH CTE AS (
SELECT id, attribute, value
FROM таблица
)
SELECT id,
MAX(CASE WHEN attribute = 'Height' THEN value END) AS Height,
MAX(CASE WHEN attribute = 'Width' THEN value END) AS Width
FROM CTE
GROUP BY id;
Визуализация
Представьте, что вы вращаете сервировку стола так, чтобы у каждого гостя тарелки сменили свои местами со стаканами:
Перед транспонированием:
| 🍽️ Настройка стола | Тарелка Алисы 🍛 | Тарелка Боба 🍛 | Тарелка Кэрол 🍛 |
| -------------------- | ---------------- | --------------- | ---------------- |
| Стакан Алисы 🥛 | Вода | | |
| Стакан Боба 🥛 | | Газировка | |
| Стакан Кэрол 🥛 | | | Сок |
После изменения:
После транспонирования:
| 🔄 Изменённая сервировка | Стакан Алисы 🥛 | Стакан Боба 🥛 | Стакан Кэрол 🥛 |
| ------------------------ | ---------------- | -------------- | ---------------- |
| Тарелка Алисы 🍛 | Вода | | |
| Тарелка Боба 🍛 | | Газировка | |
| Тарелка Кэрол 🍛 | | | Сок |
Таким образом, строки превращаются в столбцы, а столбцы – в строки.
Продвинутые методики транспонирования
Применение кросс-джойнов
CROSS JOIN позволит эффективно транспонировать данные для фиксированных наборов.
Выбираем простое решение
Если вам необходимо выполнить простое транспонирование, не усложняйте запрос ненужными функциями.
Приоритет производительности и точности
Измеряйте скорость выполнения запросов, контролируйте объем данных с помощью COUNT()
и убеждайтесь, что все значения сохраняются после транспонирования.
Методы отдельных СУБД
Изучите документацию вашей СУБД на предмет специфичных методов транспонирования.
Полезные материалы
- SQL Server dynamic PIVOT query? – Stack Overflow — примеры динамического использования PIVOT в SQL Server.
- Multiple options to transposing rows into columns – SQLShack — разнообразные техники транспонирования с большим числом примеров.
- Using PIVOT and UNPIVOT – SQL Server | Microsoft Learn — руководство по применению PIVOT и UNPIVOT в SQL Server.
- SQL Server: PIVOT Clause – TechOnTheNet — описание возможностей клаузы PIVOT для начинающих в области SQL.
- Different ways to transpose rows into columns in SQL Server – C# Corner — рассмотрение различных способов выполнения транспонирования в SQL Server.