loading..
Русский    English
19:56
листать

Оператор UNPIVOT стр. 2

Пусть нам требуется повернуть строку, содержащую NULL-значение в одном из столбцов.

Консоль
Выполнить
  1. WITH utest AS
  2. (SELECT 1 a, 2 b, NULL c)
  3. SELECT * FROM utest;

Т.е. вместо результата

a    b    c
1    2    NULL
мы хотим получить

a    1
b    2
c    NULL

Применим оператор UNPIVOT:

Консоль
Выполнить
  1. WITH utest AS
  2. (SELECT 1 a, 2 b, NULL c)
  3. SELECT col, value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a,b,c)
  6. ) AS unpvt;

Первая неожиданность - ошибка компиляции:

Тип столбца "c" конфликтует с типами других столбцов, указанных в списке UNPIVOT.

Это означает, что сервер неявно не преобразовал тип столбца "с", содержащий NULL, к типу первых двух столбцов, которые могут быть оценены как целочисленные.

Давайте сделаем это явно:

Консоль
Выполнить
  1. WITH utest AS
  2. (SELECT 1 a, 2 b, CAST(NULL AS INT) c)
  3. SELECT col,value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a,b,c)
  6. ) AS unpvt;

col    value
a    1
b    2

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

Первое, что приходит в голову всем, это заменить NULL каким-нибудь валидным значением, заведомо отсутствующим в столбце. Если, в соответствии с ограничениями предметной области, значения с не могут быть отрицательными, заменим NULL на -1:

Консоль
Выполнить
  1. WITH utest AS
  2. (SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
  3. SELECT col, value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a, b, c)
  6. ) AS unpvt;

 

col    value
a    1
b    2
c    -1

Остался последний шаг, о котором многие забывают, решая задачи на сайте sql-ex.ru. А именно, обратное преобразование. Вместо этого пытаются подобрать такое значение, которое позволило бы "удовлетворить" систему проверки. Иногда это получается, например, если сравнение NULL и '' (пустой строки) оценивается на сайте как true. Но понятно, что на это полагаться не стоит. Итак, обратное пребразование:

Консоль
Выполнить
  1. WITH utest AS
  2. (SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
  3. SELECT col, NULLIF(value, -1) value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a, b, c)
  6. ) AS unpvt;

col    value
a    1
b    2
c    NULL

Здесь как нельзя более кстати пришлась функция NULLIF.
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
обмен с tron
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.