CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH (''))
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

------解决方案--------------------
SQL code


select * into #c from (
select  id=1,typeid=1,typeName='三好学生',awardName='一等奖'
union
select  id=2,typeid=1,typeName='三好学生',awardName='二等奖'
union
select  id=3,typeid=2,typeName='三下',awardName='一等奖'
union
select  id=4,typeid=2,typeName='三下',awardName='二等奖'
union
select  id=5,typeid=2,typeName='三下',awardName='二等奖') t
//上面是创建临时表执行一次删掉

with cte as(
select typeid ,typeName,awardName=convert(varchar(max), awardName),id=ROW_NUMBER() over(order by typeid) from #c 
),cte2 as(
  select * from cte where id=1 union all
  select c1.typeid,c1.typeName,awardName=
  case when c1.typeid=c2.typeid then 
  convert(varchar(max),c1.awardName+','+c2.awardName)
  else 
  c1.awardName
  end
  ,c1.id from cte c1,cte2 c2 where c1.id=c2.id+1
)
 select typeid,typeName,min(awardName) from cte2 group by typeid,typeName

------解决方案--------------------
C# code


select distinct typeid,typename,dbo.f_getName(typeid) from testa


create function f_getName(@typeid int) returns  varchar(200) as
begin
  declare @return varchar(200)
  set @return=''
  select @return=@return+awardName+',' from testa where typeid=@typeid
  return case when @return='' then '' else substring(@return,1,len(@return)-1) end
end