有表A如下no attr1 attr2
11 a 1
11 c 2
22 a 1
33 b 2希望得到如下结果no attr
11 a,c
11 1,2
22 a
22 1
33 b
33 2
其中11中的a,c和1,2的顺序要对应上,请问该如何实现,谢谢!
11 a 1
11 c 2
22 a 1
33 b 2希望得到如下结果no attr
11 a,c
11 1,2
22 a
22 1
33 b
33 2
其中11中的a,c和1,2的顺序要对应上,请问该如何实现,谢谢!
union select wmsys.wm_concat(attr2) attr from tb where no='11' group by no
union select attr1 from tb where no<>'11'
union select attr2 from tb where no<>'11'
no int,
attr1 varchar2(1),
attr2 varchar2(1));insert into a values(11,'a','1');
insert into a values(11,'c','2');
insert into a values(22,'a','1');
insert into a values(33,'b','2');column attr format a20SELECT NO, attr
FROM (SELECT NO, group1, wmsys.wm_concat (attr1) attr
FROM (SELECT NO, '0' group1, attr1
FROM a
UNION ALL
SELECT NO, '1' group1, attr2
FROM a)
GROUP BY NO, group1);
select no,attr
from
(select no,wmsys.wm_concat(attr1) attr from tb where no='11' group by no
union select no,wmsys.wm_concat(attr2) attr from tb where no='11' group by no
union select no,attr1 from tb where no <>'11'
union select no,attr2 from tb where no <>'11'
)
order by no,attr desc
--ORACLE 10G
SELECT no,WMSYS.WM_CONCAT(attr1) AS ATTR FROM A
UNION ALL
SELECT NO,WMSYS.WM_CONCAT(ATTR2) FROM A
SELECT * FROM
(SELECT no,WMSYS.WM_CONCAT(attr1) AS ATTR FROM A GROUP BY NO
UNION ALL
SELECT NO,WMSYS.WM_CONCAT(ATTR2) FROM A GROUP BY NO)
ORDER BY NO
--ORACLE 9icreate table a(
no int,
attr1 char(2),
attr2 char(2));
)
;
insert into a values(11,'a','1');
insert into a values(11,'c','2');
insert into a values(22,'a','1');
insert into a values(33,'b','2');
select no ,substr(arr,2)attr from (
select no,max(sys_connect_by_path(attr1,',')) arr
from (
select no,attr1,no+row_number() over( order by no) rn,row_number() over(partition by no order by no) rn1
from a
)
start with rn1=1
connect by rn-1=prior rn
group by no
union all
select no,max(sys_connect_by_path(attr2,',')) arr
from (
select no,attr2,no+row_number() over( order by no) rn,row_number() over(partition by no order by no) rn1
from a
)
start with rn1=1connect by rn-1=prior rn
group by no
)
group by no,arr