Перестройка данных с UNPIVOT и включение названия столбцов

Быстрый ответ

Для выполнения трансформации столбцов в строки в SQL Server используйте CROSS APPLY в сочетании с VALUES, тогда как в PostgreSQL применяется UNNEST совместно с ARRAY. Примеры вы можете увидеть ниже.

SQL Server:

SELECT ID, Attrib, Val
FROM YourTable
CROSS APPLY (VALUES ('Col1', Col1), ('Col2', Col2)) AS CA(Attrib, Val)

PostgreSQL:

SELECT ID, c.Attrib, v.Val
FROM YourTable, UNNEST(ARRAY['Col1', 'Col2']) AS c(Attrib), UNNEST(ARRAY[Col1, Col2]) AS v(Val)
WHERE c.tableoid = v.tableoid

Выше указанные запросы преобразуют столбцы в строки, извлекая при этом пары «имя-значение».

Управление нулевыми значениями, дубликатами и производительностью

Обработка нулевых значений

Чтобы поддерживать целостность данных и не допускать появления пустых значений, отфильтруйте их с помощью оператора WHERE:

SELECT ID, Attrib, Val
FROM YourTable
CROSS APPLY (
  VALUES 
  ('Col1', Col1), 
  ('Col2', Col2)
) AS CA(Attrib, Val)
WHERE Val IS NOT NULL

Подробнее об этом расскажет наш спикер на видео

Борьба с дублирующимися данными

С помощью CROSS JOIN и конструкции CASE можно избежать появления дубликатов, создавая несколько строк на основе одной:

SELECT St.StudentName, S.Subject, 
CASE S.Subject 
  WHEN 'History' THEN St.History
  WHEN 'Math' THEN St.Math
  WHEN 'Science' THEN St.Science
END AS Marks
FROM StudentMarks St
CROSS JOIN (
  VALUES ('History'), ('Math'), ('Science')
) AS S(Subject)
WHERE St.[Subject] IS NOT NULL

Повышение производительности

С ростом объема данных скорость выполнения запросов может снижаться. Для оптимизации производительности предлагается:

• Ограничивать вывод: используйте WHERE, чтобы исключать ненужные строки.
• Бережливо истреблять ресурсы: применяйте временные или табличные переменные для временного хранения данных.
• Настройка быстродействия: оптимизировать индексы, используемые для соединения и фильтрации столбцов.

Визуализация

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

Книжная полка 📚: 
| История | Математика | Наука |
| :-----: | :--------: | :---: |
| 500     | 300        | 400   |

Превратим эту книжную полку в альбом:

Альбом 📒:
| Предмет   | Страницы |
| --------- | -------- |
| История   | 500      |
| Математика| 300      |
| Наука     | 400      |

UNPIVOT переводит каждый столбец "книжной полки" в отдельную строку "альбома".

Лучшие практики и полезные советы

Разнообразие методов

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

Практика делает мастера

Совершенствуйте свои навыки на практике. Воспользуйтесь SQL Fiddle для безопасных экспериментов.

SQL Fiddle

SQL Fiddle – это ваша лаборатория для SQL-экспериментов. Здесь вы можете:

• Тестировать запросы без риска для рабочих данных,
• Обсуждать и совместно работать с коллегами.

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

1. Инструкция по использованию PIVOT и UNPIVOT в SQL Server — Исчерпывающий гайд со синтаксической информацией.
2. Методы транспонирования строк в столбцы — Коллекция методик для транспонирования данных.
3. Динамический PIVOT в SQL Server — Статья о динамическом использовании PIVOT.
4. Обсуждение динамического UNPIVOT на Stack Overflow — Обсуждение использования UNPIVOT.