Non-MODEL Solutions
Other techniques exist to collect rows into strings. The following links will give you further insight into these approaches.
- Oracle ACE Tim Hall explains using dedicated functions for string aggregation in a very good article at ORACLE-BASE - String Aggregation Techniques. The article also covers some of the techniques presented in the SQL Snippets tutorials you just read.
- Oracle Certified Master Laurent Schneider describes an XQuery based technique on his blog page at Laurent Schneider: stragg in 10gR2 (note that XQuery features are not available in Oracle 10g Express Edition (XE) ).
- OTN user Aketi Jyuuzou suggests an XML based solution and a hierarchical solution at OTN SQL and PL/SQL Forum - Re: how to display empno in a single row instead of displaying it vertically. The XML solution performs comparably to the one presented in XML - CSV Method.
MODEL Solutions
The following MODEL based solutions have been posted by various users on Oracle discussion forums. They all perform comparably to the MODEL Method 1 solution presented earlier in this section.
(Note: Tests were performed on a 100,000 row table and resulted in 1000 rows of output with 100 comma separated values per row. The solutions presented here have been adapted from their original form to use the same test tables and columns.)
Baseline
Here is the MODEL Method 1 solution for comparison.
SQL Snippets
MODEL Method 1:
select group_key, substr( string, 2 ) as string
from t2
model
return updated rows
partition by( group_key )
dimension by
( row_number() over (partition by group_key order by val )
as position
)
measures( cast( val as varchar2(300) ) as string )
rules
upsert
iterate( 100 )
until ( presentv(string[iteration_number+2],1,0) = 0 )
( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_key ;
Test Complete
Solution 1
Frank Zhou posted this approach at Ask Tom "STRAGG" - A 10G alternative to my 9I pure SQL STRAGG soultion.
Solution 1:
select group_key, string
from
( select group_key, string, position, val_count
from t2
model
partition by( group_key )
dimension by
( row_number() over ( partition by group_key order by val ) as position )
measures
( val ,
cast( null as varchar2(300) ) as string ,
count(*) over ( partition by group_key ) val_count
)
rules
(
string[ any ] order by position =
case
when val[ cv()-1 ] is null then val[ cv() ]
else string[ cv()-1 ] || ',' || val[ cv() ]
end
)
) t2a
where val_count = position
order by group_key ;
Test Complete
Solution 2
Rob van Wijk posted this variation of Solution 1 at OTN SQL and PL/SQL Forum: Re: Looking for the following output in PLSQL.
Solution 2:
select group_key, string
from
(
select group_key, position, substr( string, 2 ) as string
from t2
model
partition by( group_key )
dimension by
( row_number() over( partition by group_key order by val desc )
as position
)
measures( cast(val as varchar2(300)) string )
rules
( string[any] order by position desc =
string[cv()+1] || ',' || string[cv()]
)
) t2a
where position = 1
order by group_key ;
Test Complete
Results
The following table shows Elapsed Time, Total Latch Gets, and statistics where values for one test differ by more than 100 from another test.
SQL Snippets
Metric MODEL Method 1 Solution 1 Solution 2
-------------------------- -------------- ---------- ----------
Elapsed Time (1/100 sec) 205 227 183
Latch Gets Total 2,451 2,359 2,078
session pga memory max 37,158,912 37,093,376 36,634,624
session uga memory max 36,139,680 36,074,216 35,637,964
sorts (rows) 103,075 203,075 203,075
redo size 2,820 2,688 2,688
See Statistics Descriptions and Latches and Internal Locks for metric descriptions.