求SQL语句：合并子表内容数据

[javamy024]

如我有
学生表
ID 名称
1 小王
2 小张
3 小李

学生专业表
ID 学生ID 专业名称
1 1 计算机
2 1 商务英语
3 2 电子自动化
4 2 原子信息
5 3 对外贸易
------------------
然后
如何写一条SQL得出结果为
ID 名称 专业
1 小王 计算机，商务英语
2 小张 电子自动货，原子信息
3 小李 对外贸易

我只知道写
select ID,名称,(select top 1 专业 from 专业表 where 学生ID = ID） From 学生
但我这样只能得到第一个专业，没有办法得到所有专业的合并

那正确的应该怎么写呢？
万分感谢

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-07-02 10:16:10
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[学生表]
if object_id('[学生表]') is not null drop table [学生表]
go 
create table [学生表]([ID] int,[名称] varchar(4))
insert [学生表]
select 1,'小王' union all
select 2,'小张' union all
select 3,'小李'
--> 测试数据：[学生专业表]
if object_id('[学生专业表]') is not null drop table [学生专业表]
go 
create table [学生专业表]([ID] int,[学生ID] int,[专业名称] varchar(10))
insert [学生专业表]
select 1,1,'计算机' union all
select 2,1,'商务英语' union all
select 3,2,'电子自动化' union all
select 4,2,'原子信息' union all
select 5,3,'对外贸易'
--------------开始查询--------------------------
;with f as
(
select a.id,a.名称,b.专业名称 from [学生表] as a inner join [学生专业表] as b on a.ID=b.学生ID
)

select id,名称,[专业名称]=stuff((select ','+[专业名称] from f where id=t.id for xml path('')), 1, 1, '') 
from f as t
group by id,名称
----------------结果----------------------------
/* id          名称   专业名称
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           小王   计算机,商务英语
2           小张   电子自动化,原子信息
3           小李   对外贸易

(3 行受影响)

*/

[javamy024]

楼上的可以 但能不能写成像 select ID,名称,(select top 1 专业 from 专业表 where 学生ID = ID） From 学生 这样方式的，而不是GroupBy的呢？

[發糞塗牆]

引用 2 楼 javamy024 的回复:

楼上的可以 但能不能写成像 select ID,名称,(select top 1 专业 from 专业表 where 学生ID = ID） From 学生 这样方式的，而不是GroupBy的呢？

写法不仅仅只有一种的啊

[霜寒月冷]

select ID,名称
,(select  专业名称 +',' from  学生专业表 t2
where t2.学生ID=t1.ID group by 专业名称 for xml path('')
)as [专业]
 from 学生表 t1

这种么

[--小F--]

引用 4 楼 chz415767975 的回复:

select ID,名称
,(select  专业名称 +',' from  学生专业表 t2
where t2.学生ID=t1.ID group by 专业名称 for xml path('')
)as [专业]
 from 学生表 t1

这种么

恩 你这种简单明了