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 杭州某工厂,杭州某工厂
*/
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 杭州某工厂,杭州某工厂
*/
SQL运行都OK的,这边有个麻烦的转oracle的工具,各种报错,不支持稍微复杂点的写法,我还不得不用这个工具,蛋疼
select fitemid,listagg(fsupply,',') within group(order by fsupply) as fsupply
from tb
group by fitemid
SQL运行都OK的,这边有个麻烦的转oracle的工具,各种报错,不支持稍微复杂点的写法,我还不得不用这个工具,蛋疼Oracle的
select fitemid, wmsys.wm_concat(fsupply) from t group by fitemid;
SQL运行都OK的,这边有个麻烦的转oracle的工具,各种报错,不支持稍微复杂点的写法,我还不得不用这个工具,蛋疼Oracle的
select fitemid, wmsys.wm_concat(fsupply) from t group by fitemid;
不能直接写oracle的,有没有简单的sql的方法
能帮我写个oracle的函数么
有没有不用函数直接sql能做到的
能帮我写个oracle的函数么wmsys.wm_concat和listagg都不能用么? sys_connect_by_path也不能用……
create table test_idle(fitemid varchar2(255),fsupply varchar2(255));insert into test_idle VALUES('A','Hangzhou');
insert into test_idle VALUES('B','Hangzhou');
insert into test_idle VALUES('A','Huzhou');
insert into test_idle VALUES('C','Ningbo');
insert into test_idle VALUES('D','Hangzhou');
insert into test_idle VALUES('B','Shaoxing');
insert into test_idle VALUES('A','Jinhua');
insert into test_idle VALUES('D','Hangzhou');CREATE FUNCTION CONCAT_STR(group_base_column VARCHAR2)
RETURN VARCHAR2 IS
v_str VARCHAR2(2000) := '';
BEGIN
FOR i IN ( SELECT fsupply FROM test_idle WHERE fitemid = group_base_column ) LOOP
v_str := v_str||i.fsupply||',';
END LOOP;
RETURN RTRIM(v_str,',');
END;
/
SELECT distinct fitemid, CONCAT_STR(fitemid) FROM test_idle;
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 杭州某工厂,杭州某工厂*/