Согласно Ицику Бен-Гану в внутри Microsoft SQL Server 2008: T- SQL Querying, SQL Server выполняет три шага при развороте таблицы:
- Создание копий
- Извлечь элементы
- Удалить строки с NULL
Шаг 1. Создание копий
Создается виртуальная таблица, в которой есть копия каждой строки в исходной таблице для каждого столбца, который не сводился. Кроме того, строка символов имени столбца хранится в новом столбце (назовем его столбцом QuestionName). *Примечание: я изменил значение в одном из ваших столбцов на NULL, чтобы показать весь процесс.
UserID UserName AnswerTo1 AnswerToQ2 AnswerToQ3 QuestionName
1 John 1 0 1 AnswerToQuestion1
1 John 1 0 1 AnswerToQuestion2
1 John 1 0 1 AnswerToQuestion3
2 Mary 1 NULL 1 AnswerToQuestion1
2 Mary 1 NULL 1 AnswerToQuestion2
2 Mary 1 NULL 1 AnswerToQuestion3
Шаг 2. Извлечение элементов
Затем создается другая таблица, в которой создается новая строка для каждого значения из исходного столбца, соответствующего значению строки символов в столбце QuestionName. Значение сохраняется в новом столбце (назовем его столбцом ответа).
UserID UserName QuestionName Response
1 John AnswerToQuestion1 1
1 John AnswerToQuestion2 0
1 John AnswerToQuestion3 1
2 Mary AnswerToQuestion1 1
2 Mary AnswerToQuestion2 NULL
2 Mary AnswerToQuestion3 1
Шаг 3. Удалите строки со значениями NULL
На этом шаге отфильтровываются все строки, созданные с нулевыми значениями в столбце Response. Другими словами, если какой-либо из столбцов AnswerToQuestion имеет нулевое значение, он не будет представлен как несводная строка.
UserID UserName QuestionName Response
1 John AnswerToQuestion1 1
1 John AnswerToQuestion2 0
1 John AnswerToQuestion3 1
2 Mary AnswerToQuestion1 1
2 Mary AnswerToQuestion3 1
Если вы выполните эти шаги, вы можете
- ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ всех строк в таблице с именем каждого столбца AnswerToQuestion для получения копий строк
- Заполните столбец Response на основе совпадения исходного столбца и QuestionName.
- Удалите значения NULL, чтобы получить те же результаты без использования UNPIVOT.
Пример ниже:
DECLARE @t1 TABLE (UserID INT, UserName VARCHAR(10), AnswerToQuestion1 INT,
AnswertoQuestion2 INT, AnswerToQuestion3 INT
)
INSERT @t1 SELECT 1, 'John', 1, 0, 1 UNION ALL SELECT 2, 'Mary', 1, NULL, 1
SELECT
UserID,
UserName,
QuestionName,
Response
FROM (
SELECT
UserID,
UserName,
QuestionName,
CASE QuestionName
WHEN 'AnswerToQuestion1' THEN AnswerToQuestion1
WHEN 'AnswerToQuestion2' THEN AnswertoQuestion2
ELSE AnswerToQuestion3
END AS Response
FROM @t1 t1
CROSS JOIN (
SELECT 'AnswerToQuestion1' AS QuestionName
UNION ALL SELECT 'AnswerToQuestion2'
UNION ALL SELECT 'AnswerToQuestion3'
) t2
) t3
WHERE Response IS NOT NULL
person
8kb
schedule
10.09.2010