表结构如下
表名为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
请各位大虾给出查询语句
解决方案 »
- 问一个SQL问题
- 一个比较困难的查询统计,高手请进
- 100分,如何避免ORACLE的死锁
- 急急!!在线等~~查看某个oracle用户的表空间大小??
- 菜鸟问题(有关删除表内记录的问题...........高手进........)
- 高分求:Oracle新手面临的难题.如何取得最近更新记录.
- 我们使用一个用oracle作后台的系统,我想看看他的表结构,有可能吗?如何操作?
- ORACLE有关包的创建问题?
- Oracle 11g提供的自动增加分区功能可以指定分区名字吗?
- 求助!64位win7,安装plsql出错!
- ORACLE 日期参数格式怎么计算啊?各位高手,散分了
- oracle 监听器自动关闭的问题
(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