SQL 多条记录赋值多个变量

create table t1
(id int identity(1,1),
name varchar(2))

create table t2
(id int,
num int)

insert into t1(name) values('A')
insert into t1(name) values('B')

insert into t2(id,num) values(1,11)
insert into t2(id,num) values(1,111)
insert into t2(id,num) values(2,22)
insert into t2(id,num) values(1,222)
insert into t2(id,num) values(1,2222)

select * from t1
select * from t2;

with
p1 as
(select a.id,a.name,[num] from t1 as a inner join t2 as b on a.id=b.id),
p2 as
(select row_number() over(partition by id order by [num]) as rownum,id,name,[num] from p1)
SELECT id,name,[1],[2],[3]
FROM p2
PIVOT(SUM([num]) FOR rownum IN ([1],[2],[3])) as pvt