Как объединить данные из двух столбцов в один без использования union и join?

    Моисеенко С.И. (22-08-2008)

Такие вопросы с завидной регулярностью появляются на страницах различных форумов. К слову сказать, для меня до сих пор остается загадкой, почему при этом ставится дополнительное условие не использовать UNION и/или JOIN. Могу лишь предположить, что это вопросы, которые задают на собеседовании при приеме на работу.

Лирическое отступление. Догадываюсь, как ответил бы на этот вопрос Джо Селко: налицо ошибка проектирования, состоящая в том, что один атрибут расщеплен на два. Однако оставим в стороне вопросы проектирования и перейдем к решению этой задачи.

Создадим тестовую таблицу и добавим в нее немного данных:

CREATE TABLE T (

col1 INT

, col2 INT

)

GO

INSERT INTO T

SELECT 1, 1

UNION ALL SELECT 1, 3

UNION ALL SELECT NULL, NULL

UNION ALL SELECT NULL, 2

GO

Итак, имеется таблица T, которая содержит два столбца с данными одного типа:

SELECT col1, col2

FROM T

col1 col2

1 1

1 3

NULL NULL

NULL 2Требуется получить следующий результат:

col

1

1

NULL

NULL

1

3

NULL

2

Мне известны три способа, реализуемых стандартными средствами интерактивного языка SQL.

1. Union all

Очевидное решение, не требующее комментариев. Заметим лишь, что UNION не подходит для решения этой задачи, т.к. устраняет дубликаты.

SELECT col1 col FROM T

UNION ALL

SELECT col2 FROM T

2. Full join

Чтобы не потерять дубликаты, находящиеся в разных столбцах, выполним полное соединение (FULL JOIN) по заведомо ложному предикату, скажем, 1 = 2:

SELECT T.col1,T1.col2

FROM T FULL JOIN T AS T1 ON 1=2

Результат:

col1 col2

1 NULL

1 NULL

NULL NULL

NULL NULL

NULL 1

NULL 3

NULL NULL

NULL 2

Далее используем функцию COALESCE, которая даст нам все, что нужно:

SELECT COALESCE(T.col1,T1.col2) col

FROM T FULL JOIN T AS T1 ON 1=2

3. Unpivot

Конструкции PIVOT и UNPIVOT появились в последних версиях стандарта SQL и были реализованы SQL Server, начиная с версии 2005. Первая из них позволяет значения в столбце вытянуть в строку, а вторая поможет нам выполнить обратную операцию:

SELECT col

FROM

(SELECT col1, col2

FROM T) p

UNPIVOT

(col FOR xxx IN

(col1, col2)

)AS unpvt

Значения из столбцов col1 и col2 собираются в одном столбце col вспомогательной таблицы unpvt. Однако есть одна особенность в использовании операторов PIVOT и UNPIVOT - они не учитывают NULL-значения. Результат последнего запроса будет таким:

col

1

1

1

3

2

Это препятствие на пути к решению нашей задачи можно преодолеть, если заменить NULL-значение на входе оператора UNPIVOT псевдозначением, т.е. значением, которого заведомо не может быть в исходных данных, а потом выполнить обратное преобразование:

SELECT NULLIF(col,777)

FROM

(SELECT COALESCE(col1,777) col1, COALESCE(col2,777) col2

FROM T) p

UNPIVOT

(col FOR xxx IN

(col1, col2)

)AS unpvt

Здесь COALESCE(colx,777) заменяет NULL-значения в столбце colx на 777, а функция NULLIF(col,777) выполняет обратное преобразование.

Последнее решение дает нам требуемый результат, однако содержит один изъян - значение 777 может рано или поздно появиться в данных, что будет приводить к неверным результатам. Чтобы устранить этот огрех, можно использовать значение другого типа, которого заведомо не может присутствовать в целочисленном столбце, например, символ 'x'. Естественно, чтобы применить этот подход, для совместимости типов целочисленный тип столбцов следует конвертировать к символьному типу, выполнив при необходимости обратное преобразование конечного результата:

SELECT CAST(NULLIF(col,'x') AS INT)

FROM

(SELECT COALESCE(CAST(col1 AS VARCHAR),'x') col1,

COALESCE(CAST(col2 AS VARCHAR),'x') col2

FROM T) p

UNPIVOT

(col FOR xxx IN

(col1, col2)

)AS unpvt

Несколько слов об эффективности представленных решений. Согласно плану выполнения запроса, основные затраты обусловлены чтением данных (операция сканирования таблицы - Table scan). Для двух первых решений сканирование выполняется дважды, в то время как для последнего (UNPIVOT) - один раз, чем и обусловлено его двойное преимущество в производительности.

DROP TABLE T