select id,wm_concat(name) name from b group by id;
select wm_concat(name) from b group by id;
wm_concat是一个未公开函数,在10g可用 如果使用9i及以下,可用: SELECT t.id, ltrim(MAX(sys_connect_by_path(t.name, ',')), ',') name FROM (SELECT row_number() over(PARTITION BY b.id ORDER BY b.id) rn, b.* FROM b) t START WITH rn = 1 CONNECT BY PRIOR rn = rn - 1 AND t.id=PRIOR t.id GROUP BY t.id
非常感谢如果在sysbase下该怎么写???
SQL> with t as( 2 select 1 id,'eee' name from dual union all 3 select 1,'www' from dual union all 4 select 1,'ww' from dual) 5 select wm_concat(name) name 6 from t 7 group by id 8 /NAME ------------------ eee,www,ww
/* 例如: id name 1 a 1 b 2 c 2 d输出: id names 1 a,b 2 c,d */ --自己写一个不就得了 ALTER FUNCTION "DBA"."fullname"(p_id integer) RETURNS varchar(1000) BEGIN DECLARE name varchar(1000); CompanyLoop: FOR CompanyFor AS ThisCompany CURSOR FOR select sales from sale where prod_id=p_id DO SET name = name || sales||',' ; END FOR CompanyLoop; RETURN ( name ); END ------------- select prod_id,fullname(prod_id) from sale group by prod_id
create table tb(id int, name nvarchar2(25)) insert into tb select 1,'eee' from dual union all select 1,'www' from dual union all select 1,'ww' from dual select wm_concat(name) from tb group by id 已经过验证
如果使用9i及以下,可用:
SELECT t.id, ltrim(MAX(sys_connect_by_path(t.name, ',')), ',') name
FROM (SELECT row_number() over(PARTITION BY b.id ORDER BY b.id) rn, b.* FROM b) t
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND t.id=PRIOR t.id
GROUP BY t.id
SQL> with t as(
2 select 1 id,'eee' name from dual union all
3 select 1,'www' from dual union all
4 select 1,'ww' from dual)
5 select wm_concat(name) name
6 from t
7 group by id
8 /NAME
------------------
eee,www,ww
例如:
id name
1 a
1 b
2 c
2 d输出:
id names
1 a,b
2 c,d
*/
--自己写一个不就得了
ALTER FUNCTION "DBA"."fullname"(p_id integer)
RETURNS varchar(1000)
BEGIN
DECLARE name varchar(1000);
CompanyLoop:
FOR CompanyFor AS ThisCompany
CURSOR FOR
select sales from sale where prod_id=p_id
DO
SET name = name || sales||',' ;
END FOR CompanyLoop;
RETURN ( name );
END
-------------
select prod_id,fullname(prod_id) from sale
group by prod_id
create table tb(id int, name nvarchar2(25))
insert into tb
select 1,'eee' from dual union all
select 1,'www' from dual union all
select 1,'ww' from dual
select wm_concat(name) from tb group by id
已经过验证