oracle列转行，以逗号分隔，并且数据是根据2个字段分组！

[christy_fang]

oracle列转行，以逗号分隔，并且数据是要分组的！ 这个研究了一天了，简单的，不分组或者根据一个字段分组的是没有问题的，但是问题是要根据两个字段分组，试了好长时间数据总是不正确，so~ 只能来求助了！

主表结构A：eid 表计id，beid 设备id，
附表B：beid 设备id，bid 建筑物id
两个表left join值为：
eid beid bid
201 1 1
201 2 4
201 5 4
201 6 4
201 3 15
201 4 28
202 10 1
202 11 1
202 12 15

要求查询出来的格式为：
eid beid bid
201 1 1
201 2,5,6 4
201 3 15
201 4 28
202 10,11 1
202 12 15

[minitoy]

select eid,wm_concat(a.beid) beid,bid from a left join b on a.beid=b.beid group by eid,bid
union
select eid,a.beid beid,wm_concat(bid) from a left join b on a.beid=b.beid group by eid,a.beid

忘记修改第二个了，直接copy的

[minitoy]

注意不要用union all，不然会出现重复数据

[我心飞翔]

测试数据：

CREATE TABLE T42
(
    eid NUMBER(4),
    bedi NUMBER(4)
);

INSERT INTO T42 VALUES(201, 1);
INSERT INTO T42 VALUES(201, 2);
INSERT INTO T42 VALUES(201, 3);
INSERT INTO T42 VALUES(201, 4);
INSERT INTO T42 VALUES(201, 5);
INSERT INTO T42 VALUES(201, 6);
INSERT INTO T42 VALUES(202, 10);
INSERT INTO T42 VALUES(202, 11);
INSERT INTO T42 VALUES(202, 12);

CREATE TABLE T43
(
    beid NUMBER(4),
    bid NUMBER(4)
);
INSERT INTO T43 VALUES(1, 1);
INSERT INTO T43 VALUES(2, 4);
INSERT INTO T43 VALUES(5, 4);
INSERT INTO T43 VALUES(6, 4);
INSERT INTO T43 VALUES(3, 15);
INSERT INTO T43 VALUES(4, 28);
INSERT INTO T43 VALUES(10, 1);
INSERT INTO T43 VALUES(11, 1);
INSERT INTO T43 VALUES(12, 15);

测试结果：

[minitoy]

-_-!只用第一句就可以了。
还以为你需要对两列分别分组

[minitoy]

eid beid bid
select eid,wm_concat(a.beid) beid,bid from a left join b on a.beid=b.beid group by eid,bid
union
select eid,a.beid beid,wm_concat(bid) from a left join b on a.beid=b.beid group by eid,bid

[minitoy]

select eid,wm_concat(a.beid) beid,bid from a left join b on a.beid=b.beid group by eid,bid
union
select eid,a.beid beid,wm_concat(bid) from a left join b on a.beid=b.beid group by eid,bid

[dws2004]

with t as (
select 201 eid, 1  beid,1   bid from  dual union all
select 201 eid, 2  beid,4   bid from  dual union all
select 201 eid, 5  beid,4   bid from  dual union all
select 201 eid, 6  beid,4   bid from  dual union all
select 201 eid, 3  beid,15  bid from  dual union all
select 201 eid, 4  beid,28  bid from  dual union all
select 202 eid, 10 beid, 1  bid from  dual union all
select 202 eid, 11 beid, 1  bid from  dual union all
select 202 eid, 12 beid, 15 bid from  dual 
)select eid,wm_concat(beid),bid from  t
group by t.eid,t.bid

--

1	201	1	1
2	201	2,6,5	4
3	201	3	15
4	201	4	28
5	202	10,11	1
6	202	12	15

[christy_fang]

[Quote=引用 3 楼 dws2004 的回复:]

SQL code
with t as (
select 201 eid, 1 beid,1 bid from dual union all
select 201 eid, 2 beid,4 bid from dual union all
select 201 eid, 5 beid,4 bid from dual union all
select 201 eid, 6 ……
[/Quote]
。。。。。。。。。。。。。死数据当然可以这么弄，问题是数据库里面的数据是活的！

[christy_fang]

[Quote=引用 1 楼 minitoy 的回复:]
eid beid bid
select eid,wm_concat(a.beid) beid,bid from a left join b on a.beid=b.beid group by eid,bid
union
select eid,a.beid beid,wm_concat(bid) from a left join b on a.beid=b.beid group by eid……
[/Quote]
两条一起用的话会报错，单独用最后一条的话，也会报错，说“a.beid 不是 group by表达式”
不过我试了一下，好像单独用第一条就已经成功了，数据是对了。再继续测测，非常感谢！这么简单，我写的复杂多了，还没有达到这效果，汗~

[soarfish]

select a.eid,wm_concat(a.beid) beid,b.bid from a left join b on a.beid=b.beid group by a.eid,b.bid;