csdn出现过N回,简单的就用存储过程解决. 给你个我在csdn看到的,自己修改下就可以了表结构: create table TBL_TEST (TID VARCHAR2(2), NM VARCHAR2(2)) 表数据: insert into TBL_TEST2 (TID, NM) values ('1', 'a'); insert into TBL_TEST2 (TID, NM) values ('2', 'b'); insert into TBL_TEST2 (TID, NM) values ('3', 'c'); insert into TBL_TEST2 (TID, NM) values ('1', 'd'); 即为: TIDNM 1a 2b 3c 1d ---------------------- 现在想要得出下面的集合该怎么写: TIDNM 1ad 2b 3c ---------------------- 即:按TID分类,如果TID一样则num列字符串并在一起(不是sum) 注:TID、NM中的值是不固定的 我想了半天想不出来,只好找大家帮忙了存储过程: CREATE OR REPLACE function link(v_id varchar2) return varchar2 is union_nm varchar2(200); begin for cur in (select nm from TBL_TEST where tid=v_id) loop union_nm := union_nm||cur.nm; end loop; union_nm := rtrim(union_nm,1); return union_nm; end; /// select tid,link(tid) from tbl_test group by tidsql直接实现: select tid, replace(max(sys_connect_by_path(nm,',')),',','') from (SELECT tid, nm, MIN(nm) over(PARTITION BY tid) minnm ,(row_number() over(ORDER BY tid, nm)) + (dense_rank() over(ORDER BY tid)) no FROM ttt) start with nm=minnm connect by no-1 = prior no group by tid
with x AS ( SELECT '张三' NAME, 'KB101' userid FROM dual UNION ALL SELECT '张三', 'KB002' FROM dual UNION ALL SELECT '张三', 'KB113' FROM dual UNION ALL SELECT '张三', 'KB003' FROM dual UNION ALL SELECT '张三', 'KB004' FROM dual UNION ALL SELECT '张三', 'KB005' FROM dual UNION ALL SELECT '张三', 'KB013' FROM dual UNION ALL SELECT '张三', 'KB103' FROM dual UNION ALL SELECT '李四', 'KB001' FROM dual UNION ALL SELECT '李四', 'KB002' FROM dual UNION ALL SELECT '王五', 'KB002' FROM dual UNION ALL SELECT '王五', 'KB006' FROM dual UNION ALL SELECT '李四', 'KB003' FROM dual) --//--直接提取法 SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn FROM x) START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;
select M_CASE,replace(max(sys_connect_by_path(ANKEN_NO , ', ')), ', ', ' ') from (SELECT M_CASE ,ANKEN_NO ,MIN(ANKEN_NO) over(PARTITION BY M_CASE) minnm ,(row_number() over(ORDER BY M_CASE, ANKEN_NO))+(dense_rank() over(ORDER BY M_CASE)) no FROM TBL_MITUMORI_CASE2 ) start with M_CASE = minnm connect by no-1 = prior no group by M_CASE没成功
SELECT CASE_NO, ltrim(MAX(sys_connect_by_path(trim(ANKEN_NO), ',')), ',') userid FROM (SELECT CASE_NO, ANKEN_NO, row_number() over(PARTITION BY CASE_NO ORDER BY CASE_NO) rn FROM a) START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND CASE_NO = PRIOR CASE_NO GROUP BY CASE_NO ORDER BY CASE_NO;
给你个我在csdn看到的,自己修改下就可以了表结构:
create table TBL_TEST (TID VARCHAR2(2), NM VARCHAR2(2))
表数据:
insert into TBL_TEST2 (TID, NM) values ('1', 'a');
insert into TBL_TEST2 (TID, NM) values ('2', 'b');
insert into TBL_TEST2 (TID, NM) values ('3', 'c');
insert into TBL_TEST2 (TID, NM) values ('1', 'd');
即为:
TIDNM
1a
2b
3c
1d
----------------------
现在想要得出下面的集合该怎么写:
TIDNM
1ad
2b
3c
----------------------
即:按TID分类,如果TID一样则num列字符串并在一起(不是sum)
注:TID、NM中的值是不固定的
我想了半天想不出来,只好找大家帮忙了存储过程:
CREATE OR REPLACE function link(v_id varchar2)
return varchar2
is
union_nm varchar2(200);
begin
for cur in (select nm from TBL_TEST where tid=v_id) loop
union_nm := union_nm||cur.nm;
end loop;
union_nm := rtrim(union_nm,1);
return union_nm;
end;
///
select tid,link(tid) from tbl_test group by tidsql直接实现:
select tid, replace(max(sys_connect_by_path(nm,',')),',','') from
(SELECT tid, nm, MIN(nm) over(PARTITION BY tid) minnm
,(row_number() over(ORDER BY tid, nm)) + (dense_rank() over(ORDER BY tid)) no
FROM ttt)
start with nm=minnm
connect by no-1 = prior no
group by tid
with x AS (
SELECT '张三' NAME, 'KB101' userid FROM dual UNION ALL
SELECT '张三', 'KB002' FROM dual UNION ALL
SELECT '张三', 'KB113' FROM dual UNION ALL
SELECT '张三', 'KB003' FROM dual UNION ALL
SELECT '张三', 'KB004' FROM dual UNION ALL
SELECT '张三', 'KB005' FROM dual UNION ALL
SELECT '张三', 'KB013' FROM dual UNION ALL
SELECT '张三', 'KB103' FROM dual UNION ALL
SELECT '李四', 'KB001' FROM dual UNION ALL
SELECT '李四', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB006' FROM dual UNION ALL
SELECT '李四', 'KB003' FROM dual)
--//--直接提取法
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn FROM x)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;
select M_CASE,replace(max(sys_connect_by_path(ANKEN_NO , ', ')), ', ', ' ')
from
(SELECT
M_CASE
,ANKEN_NO
,MIN(ANKEN_NO) over(PARTITION BY M_CASE) minnm
,(row_number() over(ORDER BY M_CASE, ANKEN_NO))+(dense_rank() over(ORDER BY M_CASE)) no
FROM
TBL_MITUMORI_CASE2
)
start with M_CASE = minnm
connect by no-1 = prior no
group by M_CASE没成功
FROM
(SELECT CASE_NO, ANKEN_NO, row_number() over(PARTITION BY CASE_NO ORDER BY CASE_NO) rn FROM a)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND CASE_NO = PRIOR CASE_NO
GROUP BY CASE_NO ORDER BY CASE_NO;
我现在没oracle环境,你可以用存储过程实现,那个很好理解的,很容易懂的.那个数据我以前测试过的,是在正确的啊!
這種問題已經有很多了!
建議你看哈這個博客,里面關于行列轉換的各種方式都有啊!
http://jack198409.itpub.net/