如何通过表a得到表b
就是把每个人对应的课程用逗号连起来，求指教谢谢
[解决办法]
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
---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
李四 数学,语文
王五 语文
张三 数学,语文,英语,物理
*/
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