Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Добрый день, коллеги!

Есть данные:
vcode Col Value
code11123
code22321


PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))
возвращает NULL
code12
code1123NULL
code2NULL321


Как сделать 0 вместо NULL?
code12
code11230
code20321


Hint: результат вставляется во временную таблицу, которая заранее создаётся.
6 сен 13, 13:30    [14804246]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Можно ли сделать это без IsNull?
С ним-то понятно, как.
6 сен 13, 13:34    [14804268]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
И еще вопрос.

Возможны ли (теоретически) в результатах PIVOT строки, где все значения NULL ? Т.е. и в [1], и в [2].
6 сен 13, 13:36    [14804290]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Glory
Member

Откуда:
Сообщений: 104760
Eugene_p1
возвращает NULL

Возвращает SELECT

Eugene_p1
Как сделать 0 вместо NULL?

Использовать ISNULL
6 сен 13, 13:41    [14804326]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
o-o
Guest
Eugene_p1,
--------answer 1------------
declare @t table (vcode varchar(5), Col int, Value int)
insert into @t (vcode, Col, Value)
values ('code1',	1,	123),
       ('code2',	2,	321)
       
 select vcode, ISNULL([1], 0) as [1], isnull([2], 0) as [2]
 from @t  PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))p

--------answer 2------------
insert into @t (vcode, Col, Value)
values ('code3',	3,	null)
       
 select *
 from @t  PIVOT 
(SUM(Value) FOR Col IN ([1], [2], [3]))p
6 сен 13, 13:48    [14804387]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Eugene_p1
Можно ли сделать это без IsNull?
.

а чем вам IsNull то не угодил?
6 сен 13, 14:16    [14804598]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
o-o
Guest
StarikNavy,
наверное, сторонники ANSI-92 предпочитают юзать COALESCE:


declare @t table (vcode varchar(5), Col int, Value int)
insert into @t (vcode, Col, Value)
values ('code1',	1,	123),
       ('code2',	2,	321)
       
 select vcode, coalesce([1], 0) as [1], coalesce([2], 0) as [2]
 from @t  PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))p
6 сен 13, 14:21    [14804635]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Спасибо!

То есть, скалярная функция - единственный выход? Меня-то интересовали альтернативы.

Еще вопрос: можно ли накладывать условия на Pivoted Table?
PIVOT
(SUM(Value) FOR Col IN([1], [2])) AS Pvt
WHERE Pvt.Value .....
6 сен 13, 14:22    [14804640]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
StarikNavy
Eugene_p1
Можно ли сделать это без IsNull?
.

а чем вам IsNull то не угодил?

Хотел избежать использования скалярной функции.
6 сен 13, 14:23    [14804645]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Eugene_p1
Хотел избежать использования скалярной функции.

Если вы про то, что "скалярные функции работают медленно", то это относится только к пользовательским скалярным функциям.
6 сен 13, 14:30    [14804697]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
o-o
Guest
Eugene_p1
Еще вопрос: можно ли накладывать условия на Pivoted Table?
PIVOT
(SUM(Value) FOR Col IN([1], [2])) AS Pvt
WHERE Pvt.Value .....


вот так что-ли?
declare @t table (vcode varchar(5), Col int, Value int)
insert into @t (vcode, Col, Value)
values ('code1',	1,	123),
       ('code2',	2,	321)
              
 select vcode, coalesce([1], 0) as [1], coalesce([2], 0) as [2]
 from @t  PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))p
where [2] > 100
-----------------------------
vcode	1	2
code2	0	321
6 сен 13, 14:32    [14804718]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
o-o,

Действительно.... почему же у меня студия ругалась? Косяк где-то.

2All:

суть вопроса в том, что колонок около 20, и мне показалось некрасивым и не оптимальным писать
IsNull([1], 0) .... IsNull([20], 0)
[....]
WHERE [1] <> 0 ... OR [20] <> 0

Поэтому и спросил, что можно с этим сделать.
6 сен 13, 15:24    [14805220]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
Eugene_p1
o-o,

Действительно.... почему же у меня студия ругалась? Косяк где-то.

2All:

суть вопроса в том, что колонок около 20, и мне показалось некрасивым и не оптимальным писать
IsNull([1], 0) .... IsNull([20], 0)
[....]
WHERE [1] <> 0 ... OR [20] <> 0


Поэтому и спросил, что можно с этим сделать.
А ничего, что некоторые NULLы не пропустит WHERE?
6 сен 13, 15:26    [14805232]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Eugene_p1
мне показалось некрасивым и не оптимальным писать


вам действительно, просто показалось.
6 сен 13, 16:08    [14805566]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
FantomGood
Member

Откуда: Херсон
Сообщений: 340
чисто из интереса, без ISNULL и без скорости


declare @t table (vcode varchar(5), Col int, Value int)
insert into @t (vcode, Col, Value)
values ('code1',	1,	123),
       ('code2',	2,	321)

       
;with result  (vcode, Col, Value )
as
( select t1.vcode, t2.Col, 0 as  Value  from @t t1 , @t t2 
where t1.Col <> t2.Col 
union all 
select vcode, Col, Value from @t)
select vcode, [1] , [2]
 from result  PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))p;
6 сен 13, 16:54    [14805799]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
iap
А ничего, что некоторые NULLы не пропустит WHERE?

По идее, в выводе я должен получить строки, в которых есть хоть один заполненный столбец, а потом функция IsNull уберет NULLы. Нет?
6 сен 13, 16:59    [14805834]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
FantomGood,

Способ интересный, но более затратный, чем IsNull.
О чем, впрочем, Вы и написали.
6 сен 13, 17:03    [14805868]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
чч
Guest
Ну, коль автор хочет изврата, то можно извратиться и без ISNULL. Например:

declare @t table (vcode varchar(5), Col int, Value int)
insert into @t (vcode, Col, Value)
values ('code1',	1,	123),
       ('code2',	2,	321)



insert into @t(vcode, col, value)
select t1.vcode, t2.col, 0 from (select vcode from @t) t1
cross join (select Col from @t) t2         
left join @t s on s.vcode = t1.vcode and s.col = t2.col
where s.col is null


 select vcode, [1], [2]
 from @t  PIVOT 
(SUM(Value) FOR Col IN ([1], [2]))p
6 сен 13, 17:23    [14805961]     Ответить | Цитировать Сообщить модератору
 Re: NULL в PIVOT  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Всем спасибо! Идеи/приёмы взяты на вооружение.
6 сен 13, 18:02    [14806128]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить