尝试将数据插入到每个适当的行中,并从名为 #Test 的 Table1 到名为 #BlueTable 的 Table2 中匹配 ID 和 DayNumber
我正在处理这个
CREATE TABLE #Test (
ID int,
DayNumber nvarchar(1),
MNumber nvarchar(2),
QLetter nvarchar(1),
Comments nvarchar(60)
)
insert into #Test(ID,DayNumber,MNumber,QLetter,Comments)
values(55,'1','2','A','000000'),
(55,'5','2','A','111111'),
(66,'7','2','A','222222'),
(66,'7','2','B','333333')
CREATE TABLE #BlueTable (
ID nvarchar(40),
DayNumber nvarchar(1),
BL_MN nvarchar(2),
BL_QL nvarchar(1),
BL_CM nvarchar(60),
BL_MN2 nvarchar(2),
BL_QL2 nvarchar(1),
BL_CM2 nvarchar(60)
)
declare @i int=1
while (@i <=7)
begin
insert into #BlueTable(ID,DayNumber)
values(55,@i),
(66,@i)
set @i=@i+1
end
declare @loop int=1,
@loopWord nvarchar(MAX)=''
while(@loop<=2)-- HOW UPDATE IS RAN
begin
exec('update #BlueTable set BL_MN'+@loopWord+'=#Test.MNumber,BL_QL'+@loopWord+'=#Test.QLetter,BL_CM'+@loopWord+'=#Test.Comments from #Test
where #BlueTable.ID=#Test.ID and #BlueTable.DayNumber = #Test.DayNumber')
set @loop =@loop+1
set @loopWord=@loop
end
select * from #Test
select * from #BlueTable order by ID
drop table #Test
drop table #BlueTable
我期待至少有一些空值,但它似乎覆盖了以前的更新并填充了新数据。这就是我更新它的方式
使用时,我一直试图获得的输出应如下所示(select * from #BlueTable where BL_CM is not null order by ID):我想要的
ID | DayNumber | BL_MN | BL_QL | BL_CM | BL_MN2 | BL_QL2 | BL_CM2 |
55 1 2 A 000000 null null null
55 5 2 A 111111 null null null
66 7 2 A 222222 2 B 333333
相反,我得到的东西是这样的:我得到了什么
ID | DayNumber | BL_MN | BL_QL | BL_CM | BL_MN2 | BL_QL2 | BL_CM2 |
55 1 2 A 000000 2 A 000000
55 5 2 A 111111 2 A 111111
66 7 2 A 222222 2 A 222222
请帮忙 :(
看看这个查询。我建议你先得到你想要的结果。然后插入第二个表。
insert into #BlueTable
select
ID, DayNumber, max(case when rn = 1 then MNumber end)
, max(case when rn = 1 then QLetter end)
, max(case when rn = 1 then Comments end)
, max(case when rn = 2 then MNumber end)
, max(case when rn = 2 then QLetter end)
, max(case when rn = 2 then Comments end)
from (
select
*, rn = row_number() over (partition by ID, DayNumber order by MNumber, QLetter)
from
#Test
) t
group by ID, DayNumber
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句