create table #table(fitemid varchar(255),fsupply varchar(255))
insert into #table('A','杭州某工厂')
insert into #table('B','杭州某工厂')
insert into #table('A','湖州某工厂')
insert into #table('C','宁波某工厂')
insert into #table('D,'杭州某工厂')
insert into #table('B','绍兴某工厂')
insert into #table('A','金华某工厂')
insert into #table('D','杭州某工厂')
如何不调用函数，直接sql能得到
'A','杭州某工厂','湖州某工厂',,'金华某工厂'
‘B’,'杭州某工厂','绍兴某工厂'
'C','宁波某工厂'
'D','杭州某工厂'
各位大大帮帮忙
------解决方案--------------------
create table #table(fitemid varchar(255),fsupply varchar(255))
insert into #table VALUES('A','杭州某工厂')
insert into #table VALUES('B','杭州某工厂')
insert into #table VALUES('A','湖州某工厂')
insert into #table VALUES('C','宁波某工厂')
insert into #table VALUES('D','杭州某工厂')
insert into #table VALUES('B','绍兴某工厂')
insert into #table VALUES('A','金华某工厂')
insert into #table VALUES('D','杭州某工厂')
select a.fitemid,
stuff((select ','+fsupply from #table b
where b.fitemid=a.fitemid
for xml path('')),1,1,'') 'fsupply'
from #table a
group by a.fitemid
/*
fitemid fsupply
------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 杭州某工厂,湖州某工厂,金华某工厂
B 杭州某工厂,绍兴某工厂
C 宁波某工厂
D 杭州某工厂,杭州某工厂
*/
------解决方案--------------------
另外6楼的listagg在Oracle 11以上版本可用;在listagg可用的情况下推荐使用listagg。
------解决方案--------------------
这个不知道你那个工具报不报错，没有直接用SQL整出来的，除非用游标之类的
create table [table](fitemid varchar(255),fsupply varchar(255))
insert into [table] VALUES('A','杭州某工厂')
insert into [table] VALUES('B','杭州某工厂')
insert into [table] VALUES('A','湖州某工厂')
insert into [table] VALUES('C','宁波某工厂')
insert into [table] VALUES('D','杭州某工厂')
insert into [table] VALUES('B','绍兴某工厂')
insert into [table] VALUES('A','金华某工厂')
insert into [table] VALUES('D','杭州某工厂')
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 VARCHAR(20))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+fsupply from [table] where fitemid=@Col1
return @S
end
go
Select distinct fitemid,fsupply=dbo.F_Str(fitemid) from [table]
go
/*
fitemid fsupply
---------------------------------------------------------------------------------------------------------------------------
A 杭州某工厂,湖州某工厂,金华某工厂
B 杭州某工厂,绍兴某工厂
C 宁波某工厂
D 杭州某工厂,杭州某工厂
*/