Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Update таблицы с транспонированием без PIVOT / 8 сообщений из 8, страница 1 из 1
21.03.2019, 09:07
    #39789343
RuCosinus
RuCosinus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
Добрый день.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 09:25
    #39789360
Guf
Guf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
RuCosinus,

Потому что update работает не последовательно. Каждый кейс вычисляется для исходной строки, а не а вовсе не для "предыдущей".
Начинать писать updete лучше с select
Код: sql
1.
2.
3.
4.
5.
6.
7.
--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 в итоге останется одна, для простоты можно считать, случайная
И только тогда, когда результат селекта устраивает можно раскомментировать первую строку и закомментировать вторую.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 10:15
    #39789391
RuCosinus
RuCosinus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
Guf,

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

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

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

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

Примечание: на самом деле у меня таких полей не 3, как в упрощенном примере (year,month,day), а в разы больше. В конкретном случае (сейчас) их порядка 30.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 10:23
    #39789398
Guf
Guf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
RuCosinusПредполагаю также, что insert, в отличии от update, работает последовательно. Таблица #pID у меня заполняется первоначальными данными посредством insert по такому же принципу, как я планировал использовать update.
Ась?! Как это? Не понял, покажи кусок кода.
RuCosinusПодскажите, как лучше (правильнее, быстрее, логичнее и т.д.) решить мою задачу.
Любым который устроит лично тебя по скорости работы и удобству поддержки. Представь, что у тебя добавится/уберется один столбик в таблице и прикинь, сколько времени тебе потребуется для внесения изменений.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 10:56
    #39789435
RuCosinus
RuCosinus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
GufАсь?! Как это? Не понял, покажи кусок кода.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 11:15
    #39789453
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
RuCosinusДобрый день.

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

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
;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


IDpYearpMonthpDay119971102220190321
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 11:19
    #39789461
Guf
Guf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
RuCosinus,

Ну так!
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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


Видишь разницу?
Если ты напишешь:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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


тоже отработает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2019, 11:30
    #39789473
RuCosinus
RuCosinus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Update таблицы с транспонированием без PIVOT
court,
Это мой вариант 1 - "Использовать промежуточную таблицу, в которую я занесу данные insert-ом, а потом уже перенести update-ом в таблицу #pTemp", но все равно спасибо :)

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

С задачей все ясно, спасибо за разъяснения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Update таблицы с транспонированием без PIVOT / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]