求SQL语句!(100分)

表1: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 查询出的结果: &nbsp; &nbsp; &nbsp; <br>姓名 &nbsp; &nbsp;课程 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;姓名 &nbsp; &nbsp; &nbsp; &nbsp; 课程 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>小明 &nbsp; &nbsp;语文 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;小明 &nbsp; &nbsp;语文,数学,政治 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>小明 &nbsp; &nbsp;数学 &nbsp; (用一条SQL语句实现查询出) &nbsp;小张 &nbsp; &nbsp; &nbsp; 语文,数学 &nbsp; &nbsp;<br>小明 &nbsp; &nbsp;政治<br>小张 &nbsp; &nbsp;语文<br>小张 &nbsp; &nbsp;数学<br><br>那位大侠帮帮忙啊 谢了

先建一个自定义函数：<br>create function StrCat(@Col1 varchar(100)) <br>returns nvarchar(100) <br>as <br>begin &nbsp; &nbsp; <br>declare @S nvarchar(100) &nbsp; &nbsp;<br>select @S=isnull(@S+',','')+课程 from TableName where 姓名=@Col1 &nbsp; &nbsp; <br>return @S <br>end <br>然后：<br>Select distinct 姓名, 课程=dbo.StrCat(姓名) from TableName

/*<br>create table ##a(xm varchar(10),obj varchar(10))<br>insert ##a values('小明','语文')<br>insert ##a values('小明','数学')<br>insert ##a values('小明','政治')<br>insert ##a values('小张','语文')<br>insert ##a values('小张','数学')<br>*/<br><br>declare<br>&nbsp; @str &nbsp; varchar(1000)<br><br>set @str='select xm,'<br>select @str=@str+obj+'+'',''+' from (select distinct obj from ##a) as a<br><br>set @str=left(@str,len(@str)-5)+' as obj from (select xm,'<br>select @str=@str+'max(case when obj='''+obj+''' then obj else '''' end) as '+obj+','<br>from (select distinct obj from ##a) as a<br><br>set @str=left(@str,len(@str)-1)+' from ##a group by xm) as a'<br><br>exec(@str)

楼上正解,,,我只有顶了....

多人接受答案了。