Простой способ транспонирования строк и столбцов в 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() и убеждайтесь, что все значения сохраняются после транспонирования.

Методы отдельных СУБД

Изучите документацию вашей СУБД на предмет специфичных методов транспонирования.

Полезные материалы

1. SQL Server dynamic PIVOT query? – Stack Overflow — примеры динамического использования PIVOT в SQL Server.
2. Multiple options to transposing rows into columns – SQLShack — разнообразные техники транспонирования с большим числом примеров.
3. Using PIVOT and UNPIVOT – SQL Server | Microsoft Learn — руководство по применению PIVOT и UNPIVOT в SQL Server.
4. SQL Server: PIVOT Clause – TechOnTheNet — описание возможностей клаузы PIVOT для начинающих в области SQL.
5. Different ways to transpose rows into columns in SQL Server – C# Corner — рассмотрение различных способов выполнения транспонирования в SQL Server.