The function STRAGG(column name) is a query that can be used in SQL+ that returns a delimited list of column values from multiple records in a table and returns them in a sorted order of any kind because they are . Note there is a 4000 char limit to the string formed.
To return a comma delimited string of values for ColB, grouped by ColA;
select colA, stragg(colB) b_list
from tableX
group by ColA
To return the delimited string as a sorted list;
select colA, straggsort(stragg(colB)) b_list
from tableX
group by ColA
To return the delimited string with a piped delimiter instead of the ', '
select colA, replace(stragg(colB),', ','|') b_list
from tableX
group by ColA
Note: the STRAGGSORT(string, delim, dupval) function has a dupval parameter that tells the function what to do about duplicate values in the sorted list.
p_dup = 'I' - Ignore duplicates (treat as single occurrence)
p_dup = 'N' - Add number of entries after value in brackets
p_dup = 'D' - Repeat duplicated entries
Defaults Used:
Delimiter = ', ' (comma followed by a space)
Quoted Values = No
Sorted Values = Yes
Duplicate Values = N - count and if greater than 1 put count in brackets next to value
(other settable values are 'D' show dups, 'I' ignore dups)
Wrap to Screen on Nth Value = 5
Notes: Wrap should be set off (set wrap off)
Record separator should be set off (set recsep off)
Column should have wrap set on (i.e. if list column is 'list' then sql>column LIST wrap on)
Server output needs to be on if you want to display your default STRAGG settings (set serveroutput on)
These settings only need to be done once for your sql session unless they get changed
1. The files stragg.sql and stragghelp.sql should be placed in your sql directory. You must be signed on as system when running. You should compile the script in your system.
2. Enter the desired query information. See examples below.
3. Execute Query.
#
accept inp prompt 'Press Enter to continue..'
undefine inp
#doc
Example:
Select a list of employees by department
set wrap off
set recsep off
column list wrap on
set serveroutput on
select department, stragg(empno) list
from lremployee
where department != 'NA'
group by department
order by department;
#
accept inp prompt 'Press Enter to see results...'
undefine inp
set wrap off
set recsep off
column list wrap on
set serveroutput on
select department, stragg(empno) list
from lremployee
where department != 'NA'
group by department
order by department;
accept inp prompt 'Press Return to continue...'
undefine inp
#doc