求一条SQL语句

linjie0000 2013-06-22 01:32:02

TB1
dah fid
A 10
A 11
A 12
B 20
B 21
B 22
C 30
C 31
要出来的效果是
dah fid
A 10,11,12
B 20,21,22
C 30,31

...全文

180 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

菲飞斐妃 2013-06-27

打赏
举报

好友难度，厉害啊

习惯性蹭分 2013-06-27

打赏
举报


declare @tab table(id varchar(10),val int)
insert into @tab
select 'A',       10 union
select 'A' ,     11 union 
select 'A'  ,     12 union
select 'B'  ,     20 union
select 'B'  ,     21 union
select 'B' ,      22 union
select 'C'   ,    30 union
select 'C'  ,     31
select id,val=stuff((select ','+cast(val as varchar) from @tab where id=t.id for xml path('')),1,1,'')
 from @tab t group by id

sqlkxr 2013-06-27

打赏
举报


select a.dah,STUFF((select ','+CONVERT(varchar(10),fid) from tb1 where dah=a.dah for xml path('')) ,1,1,'')
from tb1 a group by a.dah

sqlkxr 2013-06-27

打赏
举报


create table tb1(dah varchar(10),fid int)

insert into tb1 select 'A',10
union all
select 'A',11
union all
select 'A',12
union all
select 'B',20
union all
select 'B',21
union all
select 'B',22
union all
select 'C',30
union all
select 'C',31



create function joinstr (@dah varchar(10)) returns varchar(1000)
as
begin

 declare @sql varchar(100)
 set @sql=''
 select @sql=@sql+','+convert(varchar(10),fid) from tb1  where dah=@dah
 return stuff(@sql,1,1,'')

end


select dah,dbo.joinstr(dah) fid from tb1  group by dah

---涛声依旧--- 2013-06-22

打赏
举报

WITH CTE AS (SELECT 'A' dah,10 fid UNION SELECT 'A',11 UNION SELECT 'A',12 UNION SELECT 'B',20 UNION SELECT 'B',21 UNION SELECT 'B',22 UNION SELECT 'C',30 UNION SELECT 'C',31 ) SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM CTE WHERE dah=t.dah FOR XML PATH('')),1,1,'') FROM CTE t GROUP BY t.dah

---涛声依旧--- 2013-06-22

打赏
举报

CREATE TABLE tb(dah VARCHAR(10),fid int) INSERT tb SELECT 'A' dah,10 fid UNION SELECT 'A',11 UNION SELECT 'A',12 UNION SELECT 'B',20 UNION SELECT 'B',21 UNION SELECT 'B',22 UNION SELECT 'C',30 UNION SELECT 'C',31 SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM tb WHERE dah=t.dah FOR XML PATH('')),1,1,'') FROM TB t GROUP BY dah