关于A表的一个查询返回id info map type
1 'a' 2 'dd'
1 'b' 2 'cc'
1 'c' 2 'ccc'
2 ‘e’ 3 'cce'
2 'f' 3 'ccf'
如何返回id infos type map
1 'a,b,c' 'dd,cc,ccc' 2
2 'e,f' 'cce,ccf' 3这样的格式?最好不要用子查询。
1 'a' 2 'dd'
1 'b' 2 'cc'
1 'c' 2 'ccc'
2 ‘e’ 3 'cce'
2 'f' 3 'ccf'
如何返回id infos type map
1 'a,b,c' 'dd,cc,ccc' 2
2 'e,f' 'cce,ccf' 3这样的格式?最好不要用子查询。
,map
,STUFF((SELECT ','+infos FROM TB B WHERE A.ID=B.ID AND A.map=B.map for xml path('')),1,1,'') as infos
,STUFF((SELECT ','+type FROM TB B WHERE A.ID=B.ID AND A.map=B.map for xml path('')),1,1,'') as type
FROM TB A
GROUP BY ID,map
create function dbo.f_str(@id varchar(10),@type varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
if @type='info'
begin
select @str = isnull(@str + ',' , '') + cast(info as varchar) from tb where id = @id
end
else
begin
select @str = isnull(@str + ',' , '') + cast([type] as varchar) from tb where id = @id
end
return @str
end
go
--调用函数
select id , infos = dbo.f_str(id,'info'),map,[type]=dbo.f_str(id,'type') from tb group by id