问一个简单的sql查询语句

[flower_open]

如下表：
id seq 数量 商品
01 1 1000 a1
01 2 2000 a2
01 3 2000 a3
02 1 1000 b1

要得到如下数据
||
\/
id 数量(sum) 商品(sum)
01 5000 a1,a2,a3
02 1000 b1

请问select语句怎么写啊对数量合计很容易对名称合计怎么弄啊，哪位大大写个例子给偶瞅瞅阿，谢谢！！！

[xuefeifangpei]

select id,sum(数量) from table group by id

[flower_open]

商品要变成a1,a2,a3怎么弄主要问得是这个

[powar]

功能函数：
CREATE OR REPLACE FUNCTION GET_NAME(ID IN VARCHAR2) RETURN VARCHAR2 IS
RE_NAME VARCHAR2(100);
IN_NAME VARCHAR2(100);
TYPE CUR_NANE IS REF CURSOR;
CR_PRONAME CUR_NANE;

BEGIN
OPEN CR_PRONAME FOR SELECT A.BUZITYPENAME FROM BUZI_TYPE A WHERE A.BUZITYPECODE = ID;
LOOP
FETCH CR_PRONAME INTO IN_NAME;
EXIT WHEN CR_PRONAME%NOTFOUND;
RE_NAME := RE_NAME || ',' || IN_NAME;
END LOOP;
CLOSE CR_PRONAME;

RETURN(RE_NAME);
EXCEPTION
WHEN OTHERS THEN
RE_NAME := '';
RETURN(RE_NAME);
END GET_NAME;

调用：
select COUNT(T.BUZITYPECODE),GET_NAME(T.BUZITYPECODE) AS AA from buzi_type t
GROUP BY T.BUZITYPECODE

[guanjm]

select id,sum(数量),get_shop(id) "商品" from table group by id
再写个函数
CREATE OR REPLACE Function get_shop(c_id in varchar2) return varchar2 is
CURSOR c_do IS select 商品 from table where id=c_id;
v_DO c_do%ROWTYPE;
v_name varchar2(200);
begin
OPEN c_do;
LOOP
FETCH c_do INTO v_DO;
EXIT WHEN c_do%NOTFOUND;
BEGIN
v_name:=v_name||v_do.商品||',';
end;
END LOOP;
CLOSE c_do;
return v_name;
end;

[guanjm]

来晚了

[guanjm]

还要改一下
select id,sum(数量),substr(get_shop(id),2) "商品" from table group by id
CREATE OR REPLACE Function get_shop(c_id in varchar2) return varchar2 is
CURSOR c_do IS select 商品 from table where id=c_id;
v_DO c_do%ROWTYPE;
v_name varchar2(200);
begin
OPEN c_do;
LOOP
FETCH c_do INTO v_DO;
EXIT WHEN c_do%NOTFOUND;
BEGIN
v_name:=v_name||','||v_do.商品;
end;
END LOOP;
CLOSE c_do;
return v_name;
end;

[kulama2004]

楼上的做法是正确的