How to turn a list from a select into a column of another select (Oracle) ?

A old process we had was fetching a list from a query for each player one by one, and adding it in a excel file with other data needed on the player.  

Seeing that process was slow, we decided to turn it into a query.

If we take a close look at the model we have 4 tables, 3 that will be used in the query.

The player table (PLAYER_TABLE), the guild table (GUILD_TABLE), a job table (JOB_TABLE) and a table to link the 3 tables together call job assignation (JOB_ASSIGN). 

(note this is a simple representation, the original had around 30+ fields for each of the equivalent of the player and guild table)

What we want is to display all the info of the player and all the job the player have for each guild in one row.

So one row will look like

All the job are in varchar so what we need it’s to put them in a string separated with a comma instead of having a row for each of them.

Selecting a value from another select

This is a start but the value is a string we need to make a select of the job table and get the list.

this would work for the total of job for the player.

but first we need to know how to select in a select (sql select in a select)

From Stackoverflow example:

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

This work fine if the select return only on value per entry (player) if there multiple entry and we want to put them in a single line we need something else.

Displaying a list of result (varchar) on a single line.

If we want to display a series of string (varchar) together we need to concatenate them together.

We could try to do something with the concat method, but since Oracle 11g R2 there a another method to concatenate a sql result into a string oracle with a delimiter called LISTAGG.

SELECT job.JOB_ASSIGN_ID,

   LISTAGG( job.JOB_NAME, ',') WITHIN GROUP (ORDER BY job.JOB_ASSIGN_ID)

from JOB_TABLE job  

GROUP BY job.JOB_ASSIGN_ID;

Note: db exception on GROUP BY if your select have more than one row for job.JOB_ASSIGN_ID.

Then you can add this select has a column on the previous select where we calculated the number of job.

select u.USER_ID, u.SERVER, u.CLASSNAME, u.LV, 

(SELECT LISTAGG( job.JOB_NAME, ',') WITHIN GROUP (ORDER BY  assign.USER_ID) As userJobList

from JOB_TABLE job join JOB_ASSIGN assign on job.JOB_ASSIGN_ID = assign.JOB_ASSIGN_ID

where assign.USER_ID= u.USER_ID

GROUP BY assign.USER_ID) As userJobList 

from USER_TABLE u inner join JOB_ASSIGN assign on u.USER_ID = assign.USER_ID where assign.guildid = 12 

Note that you can put compare on column inside the inner select, that how I've separated the value per user.