There are a lot of ways to do this. I have shown three ways below.
- test data:
scott_at_ORA92> SELECT col1 FROM your_table
2 /
C
-
a
b
c
d
e
f
6 rows selected.
- using pure sql:
scott_at_ORA92> SELECT MAX (SUBSTR (SYS_CONNECT_BY_PATH (col1, ','), 2))
AS col1
2 FROM (SELECT col1,
3 ROW_NUMBER () OVER (ORDER BY col1) AS rn
4 FROM your_table)
5 START WITH rn = 1
6 CONNECT BY PRIOR rn = rn - 1
7 /
COL1
a,b,c,d,e,f
- or using a function specific to your situation
- (you could also use a procedure and display the results using
dbms_output):
scott_at_ORA92> CREATE OR REPLACE FUNCTION your_func
2 RETURN VARCHAR2
3 AS
4 v_col1s VARCHAR2(32767);
5 BEGIN
6 FOR r IN
7 (SELECT col1 FROM your_table)
8 LOOP
9 v_col1s := v_col1s || ',' || r.col1;
10 END LOOP;
11 RETURN LTRIM (v_col1s, ',');
12 END your_func;
13 /
Function created.
scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT your_func FROM DUAL
2 /
YOUR_FUNC
a,b,c,d,e,f
- or using a user-defined aggregate function, such as the stragg
- function by Tom Kyte, available on asktom.oracle.com:
scott_at_ORA92> SELECT stragg (col1) FROM your_table
2 /
STRAGG(COL1)
a,b,c,d,e,f
scott_at_ORA92>
Received on Fri Sep 30 2005 - 17:45:14 CDT
