Question

SQL 多条记录赋值多个变量

变量声明: @t1 int; @t2 int; @t3 int;
表A 表B
ID NAME ID NUMBER
1 小明 1 11
2 小杰 1 111
2 22

2 222

2 2222

需要输出的结果: ID NAME @t1 @t2 @t3
1 小明 11 111 null

这SQL语句该怎么写啊?求大神解救

Answer 1

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