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

Откуда:
Сообщений: 145
Люди помогите. Как можно используя unpivot развернуть столбцы со значением NULL - сейчас они пропадают. Пробовал set ansi_nulls off - не помогает.
P.S. Использование Case не предлагать - много столбцов и много запросов.
23 мар 11, 11:53    [10412885]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
isnull()
?
23 мар 11, 12:02    [10412970]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Mih_dim
Member

Откуда:
Сообщений: 145
Knyazev Alexey
isnull()
?


Так мне нужно чтобы они оставались Null, а не преобразовывались во что-то, а потом обратно с помощью команд isNull, coalesce и т.д. Причем в этом случае нужно будет 100% знать что на то что бужет преобразовываться (заменяться) NULL не повториться в других столбцах, что при обратном преобразовании будет произведена замена лишних записей.
23 мар 11, 12:11    [10413043]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Mih_dim
Как можно используя unpivot развернуть столбцы...
Использование Case не предлагать - много столбцов и много запросов.
Pivot заменяется case`ом. UpPivot -> cross join
Примеры:
-- Pivot
set nocount on
create table #tmp (t datetime, v int)
INSERT INTO #tmp VALUES ('20110314 00:00:00', 100) INSERT INTO #tmp VALUES ('20110314 01:00:00', 104) INSERT INTO #tmp VALUES ('20110314 02:00:00', 109)
INSERT INTO #tmp VALUES ('20110314 03:00:00', 154) INSERT INTO #tmp VALUES ('20110314 04:00:00', 187) INSERT INTO #tmp VALUES ('20110314 05:00:00', 215)
INSERT INTO #tmp VALUES ('20110314 06:00:00', 228) INSERT INTO #tmp VALUES ('20110314 07:00:00', 249)INSERT INTO #tmp VALUES ('20110314 08:00:00', 270)

declare @s varchar(max)
set @s = null

;with cte as (
 SELECT row_number() over (order by t) [rn], dateadd(dd, datediff(dd,0, T), 0) [T], cast(V as varchar(9)) [V] FROM #tmp
 )
--select * from cte
select @s = isnull(@s + 'isnull(sum(case when V = ' + v + ' then V else null end), 0) [h' +cast(rn as varchar(9)) +'],',
'isnull(sum(case when V = ' + v + ' then V else null end), 0) [h' +cast(rn as varchar(9)) +'],') +char(10)
from cte

select @s = 'select min(T) [T],' + char(10) + stuff(@s, datalength(@s)-1, 1, '') + 'from #tmp'

print @s
exec(@s)
drop table #tmp


-- UnPivot
declare @t table (id int, cause varchar(max), sn1 varchar(max), sn2 varchar(max), sn3 varchar(max))
insert @t values(3841, 'причина1', '004044', '004248', '004246')
select * from @t

-- По идее через UnPivot так:
select id, cause, sn from @t unpivot (sn for tmp in (sn1,sn2,sn3)) unp

-- Вариант через cross join
select id, cause,
sn = case i when 1 then sn1 when 2 then sn2 when 3 then sn3 end 
from @t
cross join (select 1 [i] union all select 2 union all select 3) i

Самое интересное, что в данном примере план выполнения у cross join меньше, чем у UnPivot
23 мар 11, 16:30    [10414876]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Начинающий SQL 2008
Самое интересное, что в данном примере план выполнения у cross join меньше, чем у UnPivot

И что в этом интересного? Отсутствует фильтр, убирающий null.
23 мар 11, 16:38    [10414938]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Mih_dim,

А еще UpPivot заменяется через union all:
declare @t table (id int, cause varchar(max), sn1 varchar(max), sn2 varchar(max), sn3 varchar(max))
insert @t values(3841, 'причина1', '004044', '004248', '004246')
select * from @t

select id, cause, sn1 from @t
union all
select id, cause, sn2 from @t
union all
select id, cause, sn3 from @t

Давайте сюда заполненные тестовые данные и что в итоге должно получиться.
23 мар 11, 16:39    [10414945]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Начинающий SQL 2008,

можно сделать план еще меньше, написав так:
select id, cause,sn 
from @t cross apply (select sn1 sn union all select sn2 union all select sn3) i
23 мар 11, 16:41    [10414953]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iljy,
Спасибо!
23 мар 11, 16:43    [10414974]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Mih_dim
Member

Откуда:
Сообщений: 145
iljy
Начинающий SQL 2008,

можно сделать план еще меньше, написав так:
select id, cause,sn 
from @t cross apply (select sn1 sn union all select sn2 union all select sn3) i


Прикольный вариант, но мне нужно еще и название полей
Вот пример анпивота:
select *
from
(select 111 Acc,Null T1,Null T2
union all
select 222,2,2
union all
select 333,1,1
union all
select 444,Null,1 )as pvt
UNPIVOT ( Value For T in (T1,T2)) as unpvt
Результат нужен такой + записи с Null.

А вариант с union не подходит - таблица большая и полей может быть около 30 (динамика) - будет тупить а скорость критична
23 мар 11, 17:38    [10415339]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mih_dim
Прикольный вариант, но мне нужно еще и название полей

И что мешает эти названия добавить внутрь cross apply?
23 мар 11, 17:46    [10415400]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
AmKad
Member

Откуда:
Сообщений: 5308
Может когда-нибудь...
23 мар 11, 22:14    [10416260]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Mih_dim
Member

Откуда:
Сообщений: 145
iljy,

Может. надо попробовать
20 май 11, 09:37    [10682207]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Mih_dim
Member

Откуда:
Сообщений: 145
AmKad
Может когда-нибудь...


Может быть да, но нужно сейчас.
20 май 11, 09:39    [10682215]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Drvisor
Member

Откуда:
Сообщений: 2
Эх, никто так и не сделал? Ладно, помните мою доброту, пыхтел не только для себя:)
CREATE PROCEDURE Utils.UnPivot_Parameter_Table(
    --Таблица, которую будем разворачивать
    @TableName sysname,
    --Ключевые поля, которые будут в результирующей выборке(какие-нить иднтификаторы, адрес и т.д.)
    @KeyFieldNames nvarchar(max),
    --Наименования полей для разворота через запятую
    @UnPivotFieldnames nvarchar(max),
    --Наименование столбца в котором будут наименования бывших полей
    @UnpivotedValuesColumnName sysname='[Parameter]',
    --Результат в виде SQL
    @ResultSQL nvarchar(max)=null output   
    )
AS
BEGIN
  set NOCOUNT ON
  set QUOTED_IDENTIFIER ON
  declare @SQL nvarchar(max),
    @UnpivotedValuesSelect nvarchar(max),
  	@FloatValueWhenThen nvarchar(max),
  	@StringValueWhenThen  nvarchar(max),
    @FloatValueCase nvarchar(max),
    @StringValueCase nvarchar(max),
    @TableObjName sysname,
    @TableSchemaPrefix sysname,
    @flg_debug bit
    
set @flg_debug=0
    
if SUBSTRING(@TableName,1,1)='#'
  select @TableObjName='tempdb..'+@TableName,@TableSchemaPrefix='tempdb.'
else  
  select @TableObjName=@TableName,@TableSchemaPrefix=''

set  @UnpivotedValuesSelect='select '''+REPLACE(@UnPivotFieldnames,',','''
union all
select ''')+''''
/*    select 'col1' union all
  select 'col2' union all
  select 'col3' union all
  select 'col4'
  
*/  
if @flg_debug=1
 select '@UnpivotedValuesSelect=' +@UnpivotedValuesSelect

 select @FloatValueWhenThen='',@StringValueWhenThen=''
 set @SQL='
 select @FloatValueWhenThen =@FloatValueWhenThen+''
  when ''''''+c.name+'''''' then a.[''+c.name+'']''
 from '+@TableSchemaPrefix+'sys.columns c
inner join Utils.String_List_To_Table('''+@UnPivotFieldnames+''','','') lst
	on c.Name=lst.[Str]
where c.Object_id=Object_id('''+@TableObjName+''')
and c.system_type_ID<>167

 select @StringValueWhenThen =@StringValueWhenThen+''
  when ''''''+c.name+'''''' then a.[''+c.name+'']''
 from '+@TableSchemaPrefix+'sys.columns c
inner join Utils.String_List_To_Table('''+@UnPivotFieldnames+''','','') lst
	on c.Name=lst.[Str]
where c.Object_id=Object_id('''+@TableObjName+''')
and c.system_type_ID=167
'
exec sp_ExecuteSQL @SQL,
N'@FloatValueWhenThen nvarchar(max) output,@StringValueWhenThen nvarchar(max) output',
	@FloatValueWhenThen=@FloatValueWhenThen output ,
    @StringValueWhenThen=@StringValueWhenThen output
/* 	when 'col1' then a.col1
      when 'col2' then a.col2
      when 'col3' then a.col3
      when 'col4' then a.col4
*/
  
if  @flg_debug=1
 select '@FloatValueWhenThen',@FloatValueWhenThen
 union all
 select '@StringValueWhenThen',@StringValueWhenThen

select      
@FloatValueCase=Case when @FloatValueWhenThen<>''
 then  'case b.'+@UnpivotedValuesColumnName+'
 '+@FloatValueWhenThen+'
    end'
 else 'NULL'
 END,
@StringValueCase=CASE
 when @StringValueWhenThen<>''
 then 'case b.'+@UnpivotedValuesColumnName+'
 '+@StringValueWhenThen+'
    end    '
 else 'NULL'
 end
    
  set @SQL=' 
select '+@KeyFieldNames+', b.'+@UnpivotedValuesColumnName+', 
Float_Value  = '+@FloatValueCase+',
String_Value = '+@StringValueCase+'
from (
  select '+@KeyFieldNames+','+ @UnPivotFieldnames+'
  from '+@TableName+' 
  ) a
cross join (
 '+@UnpivotedValuesSelect+'
  ) b ('+@UnpivotedValuesColumnName+')'
  
if @ResultSQL is null
 exec(@SQL) 
 else 
 set @ResultSQL=@SQL

 
END
3 ноя 11, 15:42    [11547030]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
Drvisor
Member

Откуда:
Сообщений: 2
в процедуре используется функция
<src=sql>
CREATE FUNCTION Utils.String_List_To_Table (
@List NVARCHAR(MAX),
@Separator NVARCHAR(MAX) = ';'
)
RETURNS @Table_List TABLE (
[Num] INT,
[Str] NVARCHAR(MAX)
)
AS
BEGIN
IF @List IS NOT NULL AND @Separator IS NOT NULL
BEGIN
DECLARE @I INT, @Pos INT, @Num INT, @Len INT
SET @I = 1
SET @Pos = 1
SET @Num = 1
SET @Len = LEN(@Separator + '*') - 1

WHILE @I <> 0
BEGIN
SET @I = CHARINDEX(@Separator, @List, @Pos)
IF @I <> 0
BEGIN
INSERT INTO @Table_List VALUES (@Num, NULLIF(SUBSTRING(@List, @Pos, @I - @Pos), ''))
SET @Pos = @I + @Len
SET @Num = @Num + 1
END
ELSE
INSERT INTO @Table_List VALUES (@Num, NULLIF(RIGHT(@List, LEN(@List+'*')-1 - @Pos + 1), ''))
END
END
RETURN
END
</src>
8 ноя 11, 14:35    [11563853]     Ответить | Цитировать Сообщить модератору
 Re: Unpivot и NULL (очень надо)  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Drvisor, есть другие способы преобразования строки в таблицу. Можно через таблицу чисел (способ от iap) или через xml. Как по мне - работать будет шустрее нежели циклы. Хотя тестировать лень.

* Сам разворот особо не глядел, слишком уж типовая задача.
8 ноя 11, 15:36    [11564380]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить