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

Откуда:
Сообщений: 51
 		Добрый день.

Возникла необходимость проапдейтить таблицу, по правилам, понятным из кода ниже.
Никак не могу понять, почему не работает код, который у меня в голове работает великолепно :)
Где-то явно у меня не хватает знаний, но не пойму где. Конкретно в моем случае, можно, наверно, перевернуть таблицу PIVOT-ом или сделать просто несколько последовательных update, но я хочу понять в чем моя ошибка на будущее.
Подскажите, что же не так...

IF OBJECT_ID(N'tempdb.dbo.#pID',N'U') IS NOT NULL
	DROP table dbo.#pID
create table #pID
(
ID numeric(15,0)
,pYear varchar(4)
,pMonth varchar(2)
,pDay varchar(2)
)

IF OBJECT_ID(N'tempdb.dbo.#pTemp',N'U') IS NOT NULL
	DROP table dbo.#pTemp
create table #pTemp
(
ID numeric(15,0)
,Type varchar(6)
,Value varchar(4)
)

insert into #pID
(ID,pYear,pMonth,pDay)
VALUES
(1,null,null,null)
,(2,null,null,null)

insert into #pTemp
(ID,Type,Value)
VALUES
(0,'Year','2005')
,(0,'Month','06')
,(0,'Day','26')
,(1,'Year','1997')
,(1,'Month','11')
,(1,'Day','02')
,(2,'Year','2019')
,(2,'Month','03')
,(2,'Day','21')


select *
from #pID pid
left join #pTemp pt on pt.ID = pid.ID

update pid
set pid.pYear = case when pt.Type = 'Year' then pt.Value else isnull(pid.pYear,0) end
	,pid.pMonth = case when pt.Type = 'Month' then pt.Value else isnull(pid.pMonth,0) end
	,pid.pDay = case when pt.Type = 'Day' then pt.Value else isnull(pid.pDay,0) end
from #pID pid
left join #pTemp pt on pt.ID = pid.ID

select *
from #pID pid
21 мар 19, 09:07    [21839130]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
 		RuCosinus,

Потому что update работает не последовательно. Каждый кейс вычисляется для исходной строки, а не а вовсе не для "предыдущей".
Начинать писать updete лучше с select
--update pid set 
select pid.ID,
pid.pYear = case when pt.Type = 'Year' then pt.Value else isnull(pid.pYear,0) end
	,pid.pMonth = case when pt.Type = 'Month' then pt.Value else isnull(pid.pMonth,0) end
	,pid.pDay = case when pt.Type = 'Day' then pt.Value else isnull(pid.pDay,0) end
from #pID pid
left join #pTemp pt on pt.ID = pid.ID

из трех строк для каждого pid.ID в итоге останется одна, для простоты можно считать, случайная
И только тогда, когда результат селекта устраивает можно раскомментировать первую строку и закомментировать вторую.
21 мар 19, 09:25    [21839156]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
RuCosinus
Member

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

Ага, я понял вроде. Предполагаю также, что insert, в отличии от update, работает последовательно. Таблица #pID у меня заполняется первоначальными данными посредством insert по такому же принципу, как я планировал использовать update.
Подскажите, как лучше (правильнее, быстрее, логичнее и т.д.) решить мою задачу.

Вариант 1
Использовать промежуточную таблицу, в которую я занесу данные insert-ом, а потом уже перенести update-ом в таблицу #pTemp

Вариант 2
Использовать подзапрос в котором попробовать развернуть эту таблицу через PIVOT

Вариант 3
Последовательно написать 3 update

Примечание: на самом деле у меня таких полей не 3, как в упрощенном примере (year,month,day), а в разы больше. В конкретном случае (сейчас) их порядка 30.
21 мар 19, 10:15    [21839206]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
RuCosinus
Предполагаю также, что insert, в отличии от update, работает последовательно. Таблица #pID у меня заполняется первоначальными данными посредством insert по такому же принципу, как я планировал использовать update.

Ась?! Как это? Не понял, покажи кусок кода.
RuCosinus
Подскажите, как лучше (правильнее, быстрее, логичнее и т.д.) решить мою задачу.

Любым который устроит лично тебя по скорости работы и удобству поддержки. Представь, что у тебя добавится/уберется один столбик в таблице и прикинь, сколько времени тебе потребуется для внесения изменений.
21 мар 19, 10:23    [21839220]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
RuCosinus
Member

Откуда:
Сообщений: 51
Guf
Ась?! Как это? Не понял, покажи кусок кода.


Транспонированные данные, которые мне нужно свести в одну таблицу лежат в двух. Сначала я вставляю кусок данных из первой таблицы, а потом пытвлся проапдейтить по тому же принципу из второй. Пример ниже

IF OBJECT_ID(N'tempdb.dbo.#pID',N'U') IS NOT NULL
	DROP table dbo.#pID
create table #pID
(
ID numeric(15,0)
,pYear varchar(4)
,pMonth varchar(2)
,pDay varchar(2)
,pTemp varchar(10)
,pWind varchar(10)
,pHumidity varchar(10)
)

IF OBJECT_ID(N'tempdb.dbo.#pTemp',N'U') IS NOT NULL
	DROP table dbo.#pTemp
create table #pTemp
(
ID numeric(15,0)
,Type varchar(6)
,Value varchar(4)
)

IF OBJECT_ID(N'tempdb.dbo.#pTemp2',N'U') IS NOT NULL
	DROP table dbo.#pTemp2
create table #pTemp2
(
ID numeric(15,0)
,Type varchar(10)
,Value varchar(10)
)

insert into #pTemp2
(ID,Type,Value)
VALUES
(0,'Temp','+31')
,(0,'Wind','2м/с')
,(0,'Humidity','65%')
,(1,'Temp','-3')
,(1,'Wind','10м/с')
,(1,'Humidity','42%')
,(2,'Temp','+1')
,(2,'Wind','1м/с')
,(2,'Humidity','56%')

insert into #pID
(ID,pTemp,pWind,pHumidity)
select t.ID
		,max(case when t.Type = 'Temp' then t.Value else '' end)
		,max(case when t.Type = 'Wind' then t.Value else '' end)
		,max(case when t.Type = 'Humidity' then t.Value else '' end)
from #pTemp2 t
group by t.ID

select * from #pID
21 мар 19, 10:56    [21839281]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
court
Member

Откуда:
Сообщений: 2016
RuCosinus
Добрый день.

Возникла необходимость проапдейтить таблицу, по правилам, понятным из кода ниже.
Никак не могу понять, почему не работает код, который у меня в голове работает великолепно :)
Где-то явно у меня не хватает знаний, но не пойму где. Конкретно в моем случае, можно, наверно, перевернуть таблицу PIVOT-ом или сделать просто несколько последовательных update, но я хочу понять в чем моя ошибка на будущее.
Подскажите, что же не так...

+
IF OBJECT_ID(N'tempdb.dbo.#pID',N'U') IS NOT NULL
	DROP table dbo.#pID
create table #pID
(
ID numeric(15,0)
,pYear varchar(4)
,pMonth varchar(2)
,pDay varchar(2)
)

IF OBJECT_ID(N'tempdb.dbo.#pTemp',N'U') IS NOT NULL
	DROP table dbo.#pTemp
create table #pTemp
(
ID numeric(15,0)
,Type varchar(6)
,Value varchar(4)
)

insert into #pID
(ID,pYear,pMonth,pDay)
VALUES
(1,null,null,null)
,(2,null,null,null)

insert into #pTemp
(ID,Type,Value)
VALUES
(0,'Year','2005')
,(0,'Month','06')
,(0,'Day','26')
,(1,'Year','1997')
,(1,'Month','11')
,(1,'Day','02')
,(2,'Year','2019')
,(2,'Month','03')
,(2,'Day','21')


select *
from #pID pid
left join #pTemp pt on pt.ID = pid.ID

update pid
set pid.pYear = case when pt.Type = 'Year' then pt.Value else isnull(pid.pYear,0) end
	,pid.pMonth = case when pt.Type = 'Month' then pt.Value else isnull(pid.pMonth,0) end
	,pid.pDay = case when pt.Type = 'Day' then pt.Value else isnull(pid.pDay,0) end
from #pID pid
left join #pTemp pt on pt.ID = pid.ID

select *
from #pID pid


;with cte as (
	select pid.ID
		,nYear	=max(case when pt.Type = 'Year' then pt.Value else isnull(pid.pYear,0) end)
		,nMonth =max(case when pt.Type = 'Month' then pt.Value else isnull(pid.pMonth,0) end)
		,nDay	=max(case when pt.Type = 'Day' then pt.Value else isnull(pid.pDay,0) end)
	from #pID pid
	left join #pTemp pt on pt.ID = pid.ID
	group by pid.ID)

update pid
set   
	pid.pDay	=cte.nDay
	,pid.pMonth =cte.nMonth
	,pid.pYear	=cte.nYear  
from #pID pid inner join cte on pid.ID=cte.ID 

select *
from #pID pid

IDpYearpMonthpDay
119971102
220190321
21 мар 19, 11:15    [21839314]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
 		RuCosinus,

Ну так!
update pid
set pid.pYear = case when pt.Type = 'Year' then pt.Value else isnull(pid.pYear,0) end
	,pid.pMonth = case when pt.Type = 'Month' then pt.Value else isnull(pid.pMonth,0) end
	,pid.pDay = case when pt.Type = 'Day' then pt.Value else isnull(pid.pDay,0) end
from #pID pid
left join #pTemp pt on pt.ID = pid.ID


insert into #pID
(ID,pTemp,pWind,pHumidity)
select t.ID
		,max(case when t.Type = 'Temp' then t.Value else '' end)
		,max(case when t.Type = 'Wind' then t.Value else '' end)
		,max(case when t.Type = 'Humidity' then t.Value else '' end)
from #pTemp2 t
group by t.ID

Видишь разницу?
Если ты напишешь:
with upd
as (
select  pt.ID
      , max(case when pt.Type = 'Year'  then pt.Value end) as uYear
      , max(case when pt.Type = 'Month' then pt.Value end) as uMonth
      , max(case when pt.Type = 'Day'   then pt.Value end) as uDay
from #pTemp pt
group by pt.ID
)
--update pid set 
select pid.ID,
     pid.pYear  = isnull(upd.uYear ,0)
	,pid.pMonth = isnull(upd.uMonth,0)
	,pid.pDay   = isnull(upd.uDay  ,0)
from #pID pid
left join upd on upd.ID = pid.ID

тоже отработает.
21 мар 19, 11:19    [21839323]     Ответить | Цитировать Сообщить модератору
 Re: Update таблицы с транспонированием без PIVOT  [new]
RuCosinus
Member

Откуда:
Сообщений: 51
 		court,
Это мой вариант 1 - "Использовать промежуточную таблицу, в которую я занесу данные insert-ом, а потом уже перенести update-ом в таблицу #pTemp", но все равно спасибо :)

Guf,
так понятно, что агрегатные не применимы в update, поэтому и пытался обойтись без них. Видимо у меня в голове не сложилось почему-то применение агрегатных и невозможность update.

С задачей все ясно, спасибо за разъяснения.
21 мар 19, 11:30    [21839338]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить