请问个查询问题,高手请进!

Question

storeroom表字段：  ProductID（产品ID）  PositionID（库位ID）(varchar)
                       10001                 A1
                       10001                 B2
                       10002                 C3
                       10002                 D4
                       10002                 E5我想得到结果：  ProductID（产品ID）  Position（库位ID）(varchar)
                       10001           A1,B2
                       10002           C3,D4,E5请问如何实现？谢谢！

Answer 1

create table tb(ProductID varchar(10),PositionID varchar(10))
insert into tb 
select '10001','A1'
union all select '10001','B2'
union all select '10002','C3'
union all select '10002','D4'
union all select '10002','E5'
gocreate function dbo.fc_str(@ProductID varchar(10))
returns varchar(100)
as
begin
 declare @sql varchar(1000)
 set @sql=''
 select @sql=@sql+','+cast(PositionID as varchar(100)) from tb where ProductID=@ProductID
 return stuff(@sql,1,1,'')
end
goselect ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductIDdrop table tbdrop function dbo.fc_str