表结构如下
表名为table1数据如下
INDEX_NAME COLOMN_NAME
PK_AA COL1
PK_AA COL2
PK_BB COL3想要通过查询语句得到
INDEX_NAME COLOMN_NAME
PK_AA COL1,COL2
PK_BB COL3
请各位大虾给出查询语句
表名为table1数据如下
INDEX_NAME COLOMN_NAME
PK_AA COL1
PK_AA COL2
PK_BB COL3想要通过查询语句得到
INDEX_NAME COLOMN_NAME
PK_AA COL1,COL2
PK_BB COL3
请各位大虾给出查询语句
(in_index_name VARCHAR2)
RETURN VARCHAR2
IS
c_name VARCHAR2 (1000);
new_c_name VARCHAR2 (1000);
first_flag NUMBER; CURSOR cur_c_name
IS
SELECT colomn_name
FROM table a
WHERE a.index_name= in_index_name
ORDER BY 1;
BEGIN
first_flag := 0;
new_c_name := ''; OPEN cur_c_name; LOOP
FETCH cur_c_name
INTO c_name; EXIT WHEN cur_c_name%NOTFOUND;
IF (first_flag = 1)
THEN
new_c_name:= sale_method_name|| ',' ;
ELSE
first_flag := 1;
END IF; new_c_name := new_c_name || c_name;
END IF;
END LOOP; RETURN new_c_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
WHEN OTHERS
THEN
RETURN NULL;
END fun_c_name;========================================================然后用这样的语句
select a.index_name,fun_c_name(a.index_name)
from
(
select a.index_name
from table
group by a.index_name
)
看下这篇文章,跟你一样的问题
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,WMSYS.WM_CONCAT(COLOMN_NAME) COLOMN_NAME
8 FROM A
9 GROUP BY INDEX_NAME
10 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
方法二:
SQL> with a as (select 'PK_AA' INDEX_NAME,'COL1' COLOMN_NAME FROM DUAL
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLOMN_NAME,',')),2) COLOMN_NAME
8 FROM (SELECT INDEX_NAME,COLOMN_NAME,ROW_NUMBER()OVER(PARTITION BY INDEX_NAME ORDER BY INDEX_NAME) RN FROM A)
9 START WITH RN=1
10 CONNECT BY RN-1=PRIOR RN AND INDEX_NAME =PRIOR INDEX_NAME
11 GROUP BY INDEX_NAME
12 ORDER BY INDEX_NAME
13 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,WMSYS.WM_CONCAT(COLOMN_NAME) COLOMN_NAME
8 FROM A
9 GROUP BY INDEX_NAME
10 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
方法二:
SQL> with a as (select 'PK_AA' INDEX_NAME,'COL1' COLOMN_NAME FROM DUAL
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLOMN_NAME,',')),2) COLOMN_NAME
8 FROM (SELECT INDEX_NAME,COLOMN_NAME,ROW_NUMBER()OVER(PARTITION BY INDEX_NAME ORDER BY INDEX_NAME) RN FROM A)
9 START WITH RN=1
10 CONNECT BY RN-1=PRIOR RN AND INDEX_NAME =PRIOR INDEX_NAME
11 GROUP BY INDEX_NAME
12 ORDER BY INDEX_NAME
13 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,WMSYS.WM_CONCAT(COLOMN_NAME) COLOMN_NAME
8 FROM A
9 GROUP BY INDEX_NAME
10 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
方法二:
SQL> with a as (select 'PK_AA' INDEX_NAME,'COL1' COLOMN_NAME FROM DUAL
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLOMN_NAME,',')),2) COLOMN_NAME
8 FROM (SELECT INDEX_NAME,COLOMN_NAME,ROW_NUMBER()OVER(PARTITION BY INDEX_NAME ORDER BY INDEX_NAME) RN FROM A)
9 START WITH RN=1
10 CONNECT BY RN-1=PRIOR RN AND INDEX_NAME =PRIOR INDEX_NAME
11 GROUP BY INDEX_NAME
12 ORDER BY INDEX_NAME
13 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,WMSYS.WM_CONCAT(COLOMN_NAME) COLOMN_NAME
8 FROM A
9 GROUP BY INDEX_NAME
10 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
方法二:
SQL> with a as (select 'PK_AA' INDEX_NAME,'COL1' COLOMN_NAME FROM DUAL
2 UNION
3 select 'PK_AA' INDEX_NAME,'COL2' COLOMN_NAME FROM DUAL
4 UNION
5 select 'PK_BB' INDEX_NAME,'COL3' COLOMN_NAME FROM DUAL
6 )
7 select INDEX_NAME,SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLOMN_NAME,',')),2) COLOMN_NAME
8 FROM (SELECT INDEX_NAME,COLOMN_NAME,ROW_NUMBER()OVER(PARTITION BY INDEX_NAME ORDER BY INDEX_NAME) RN FROM A)
9 START WITH RN=1
10 CONNECT BY RN-1=PRIOR RN AND INDEX_NAME =PRIOR INDEX_NAME
11 GROUP BY INDEX_NAME
12 ORDER BY INDEX_NAME
13 /
INDEX_NAME COLOMN_NAME
---------- --------------------------------------------------------------------------------
PK_AA COL1,COL2
PK_BB COL3
from table_name
group by index_name