如何通过表a得到表b

[小V]

就是把每个人对应的课程用逗号连起来，求指教谢谢

[andy_liucj]

if object_id('Tempdb..#t') is not null drop table #t
create table #t(
id int identity(1,1) not null,
lastname nvarchar(100) null,
lession nvarchar(100) null
)
Insert Into #t
select '张三','数学' union all
select '张三','语文' union all
select '李四','数学' union all
select '张三','英语' union all
select '王五','语文' union all
select '李四','语文' union all
select '张三','物理'


select * from #t

select distinct lastname, stuff((select ','+lession from #t b where a.lastname=b.lastname for XML path('')),1,1,'') lession from #t a

[IEEE_China]

if object_id('Tempdb..#t') is not null drop table #t
create table #t(
id int identity(1,1) not null,
lastname nvarchar(100) null,
lession nvarchar(100) null
)
Insert Into #t
select '张三','数学' union all
select '张三','语文' union all
select '李四','数学' union all
select '张三','英语' union all
select '王五','语文' union all
select '李四','语文' union all
select '张三','物理'

select t.lastname,stuff((select ','+lession from #t z where z.lastname=t.lastname for xml path('')),1,1, '')
from #t t group by lastname
 
-----------------

(7 行受影响)
lastname                                                                                             
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四                                                                                                   数学,语文
王五                                                                                                   语文
张三                                                                                                   数学,语文,英语,物理

(3 行受影响)

[發糞塗牆]

if object_id('Tempdb..#t') is not null drop table #t
create table #t(
id int identity(1,1) not null,
lastname nvarchar(100) null,
lession nvarchar(100) null
)
Insert Into #t
select '张三','数学' union all
select '张三','语文' union all
select '李四','数学' union all
select '张三','英语' union all
select '王五','语文' union all
select '李四','语文' union all
select '张三','物理'

select a.lastname,
stuff((select ','+lession from #t b 
       where b.lastname=a.lastname 
       for xml path('')),1,1,'') 'lession'
from #t a
group by  a.lastname

/*
lastname                                                                                             lession
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四                                                                                                   数学,语文
王五                                                                                                   语文
张三                                                                                                   数学,语文,英语,物理
*/