The following SQL is a modification of this example of looping through data and presenting it as a comma separated list in one row.
It gives a comma separated list of roles for each user. Note that this will only work on Oracle databases as far as I'm aware.
select ROLEUSER as USER_ID, substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES from ( select ROLEUSER, ROLENAME, count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT, ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE from PSROLEUSER where ROLEUSER is not null) where ROLECOUNT=ROLESEQUENCE start with ROLESEQUENCE = 1 connect by prior ROLESEQUENCE+1=ROLESEQUENCE and prior ROLEUSER=ROLEUSER ;
How does it work? Well here's a break down.
The query inside the FROM clause essentially groups the data for looping.
select ROLEUSER, ROLENAME, count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT, ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE from PSROLEUSER where ROLEUSER is not null
It returns the user (ROLEUSER), the name of the role ((ROLENAME), a count of the number of roles for that user ((ROLECOUNT) and a sequence number for each role row (ROLESEQUENCE).
So for example if the user PS has the roles:
- PeopleSoft Administrator
- PeopleTools
- Portal Administrator
- ProcessSchedulerAdmin
- Security Administrator
The result would look like this:
ROLEUSER | ROLENAME | ROLECOUNT | ROLESEQUENCE |
---|---|---|---|
PS | PeopleSoft Administrator | 5 | 1 |
PS | PeopleTools | 5 | 2 |
PS | Portal Administrator | 5 | 3 |
PS | ProcessSchedulerAdmin | 5 | 4 |
PS | Security Administrator | 5 | 5 |
This essentially sets up the loop - PS has 5 roles and each of them has been placed in sequence using the Oracle system ROW_NUMBER partitioned over the role user and ordered by the role name.
The outside part of the query then uses the SYS_CONNECT_BY_PATH starting with the ROLECOUNT equal to the ROLESEQUENCE which starts at 1 and is incremented by 1 each time. To ensure that we are looping for the same user each time, the SYS_CONNECT_BY_PATH uses the line prior ROLEUSER=ROLEUSER. So when we move to the next user, the loop starts again with a ROLESEQUENCE of 1.
You don't have to fully understand the syntax, but what this SQL demonstrates is how to create a simple loop in Oracle using plain old SQL.