参考: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 IDDROP TABLE #YourTable 代码来自: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
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 结果 1 三好学生 二等奖,一等奖 2 三下 二等奖,二等奖,一等奖
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 写一个函数,就可以解决了。已经测试,成功!
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 写一个函数,就可以解决了。已经测试,成功!
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 写一个函数,解决问题 !
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 IDDROP TABLE #YourTable
代码来自:
http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
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
结果
1 三好学生 二等奖,一等奖
2 三下 二等奖,二等奖,一等奖
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
写一个函数,就可以解决了。已经测试,成功!
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
写一个函数,就可以解决了。已经测试,成功!
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
写一个函数,解决问题
!