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
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 #tselect distinct lastname, stuff((select ','+lession from #t b where a.lastname=b.lastname for XML path('')),1,1,'') lession from #t a
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 行受影响)
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
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四 数学,语文
王五 语文
张三 数学,语文,英语,物理
*/
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 #tselect distinct lastname, stuff((select ','+lession from #t b where a.lastname=b.lastname for XML path('')),1,1,'') lession from #t a