create table xxs(ID int,Numyear int,Subject nvarchar(20),Score int)
insert into xxs select 1,1,'语文',80
insert into xxs select 1,1,'数学',60
insert into xxs select 2,1,'语文',50
insert into xxs select 2,1,'化学',40
insert into xxs select 2,1,'英语',60
insert into xxs select 2,2,'语文',75 select ID,Numyear,Subject+cast(Score as varchar(1000)) as '各科' into #xxs from xxs order by ID
--select * from xxs
declare @i int,
@j varchar(2000) ,
@k int
set @i = 0
set @j =''
set @k=0
update #xxs
set @j = case when @i = ID AND Numyear=@k then @j + ',' + 各科 else 各科 end,
@i =ID,
各科 = @j ,
@k=Numyear
select * from #xxs
SELECT ID,Numyear,MAX(各科) as 各科 FROM #xxs GROUP BY ID,Numyear
insert into xxs select 1,1,'语文',80
insert into xxs select 1,1,'数学',60
insert into xxs select 2,1,'语文',50
insert into xxs select 2,1,'化学',40
insert into xxs select 2,1,'英语',60
insert into xxs select 2,2,'语文',75 select ID,Numyear,Subject+cast(Score as varchar(1000)) as '各科' into #xxs from xxs order by ID
--select * from xxs
declare @i int,
@j varchar(2000) ,
@k int
set @i = 0
set @j =''
set @k=0
update #xxs
set @j = case when @i = ID AND Numyear=@k then @j + ',' + 各科 else 各科 end,
@i =ID,
各科 = @j ,
@k=Numyear
select * from #xxs
SELECT ID,Numyear,MAX(各科) as 各科 FROM #xxs GROUP BY ID,Numyear
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货