## Rows to String

### Other Techniques

#### Non-MODEL Solutions

Other techniques exist to collect rows into strings. The following links will give you further insight into these approaches.

#### 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 = string || ',' || 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.
```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11650.html]SQL Snippets: Rows to String - Other Techniques[/url]