WM_CONCAT多行根据主键合并成一行

现在需求如下：

          表1--表4中，每个字段1均为主键，要将四张表查询出的结果展示为下图。

 主要用到了WM_CONCAT函数。测试用到的表结构和表数据如下：

create table A1
(
  ID1 VARCHAR2(1),
  YI  VARCHAR2(10)
);


create table A2
(
  ID2 VARCHAR2(1),
  ER  VARCHAR2(10)
);


create table A3
(
  ID3 VARCHAR2(1),
  SAN VARCHAR2(10)
);


create table A4
(
  ID4 VARCHAR2(10),
  SI  VARCHAR2(100)
);

insert into A1 (ID1, YI)
values ('2', 'B');
insert into A1 (ID1, YI)
values ('3', 'C');


insert into A2 (ID2, ER)
values ('1', 'AA');
insert into A2 (ID2, ER)
values ('2', 'BB');


insert into A3 (ID3, SAN)
values ('1', 'AAA');
insert into A3 (ID3, SAN)
values ('3', 'CCC');


insert into A4 (ID4, SI)
values ('1', 'AAAA');
insert into A4 (ID4, SI)
values ('3', 'CCCC');
commit;

SQL语句：

SELECT TT.产品类型,
       WM_CONCAT(TT.YI),
       WM_CONCAT(TT.ER),
       WM_CONCAT(TT.SAN),
       WM_CONCAT(TT.SI)
  FROM (SELECT CASE
                 WHEN A1.ID1 IS NOT NULL THEN
                  A1.ID1
                 WHEN A2.ID2 IS NOT NULL THEN
                  A2.ID2
                 WHEN A3.ID3 IS NOT NULL THEN
                  A3.ID3
                 WHEN A4.ID4 IS NOT NULL THEN
                  A4.ID4
               END 产品类型,
               A1.YI YI,
               A2.ER ER,
               A3.SAN SAN,
               A4.SI SI
          FROM A1
          FULL JOIN A2
            ON A1.ID1 = A2.ID2
          FULL JOIN A3
            ON A1.ID1 = A3.ID3
          FULL JOIN A4
            ON A1.ID1 = A4.ID4) TT
 GROUP BY TT.产品类型

查询效果图：