How do I process data in Multivalued Column in Oracle PLSQL?

I am working on creating a small database in Oracle for a project at work. One of the columns will need to have multiple values recorded in it. What’s the query to create a multivalued column?

Answer

If you need a user to enter multiple email addresses, I would consider creating a USER_EMAIL table to store such records.

Create Table User_Email (User_Id int, Email varchar(100));

User_Id would be a foreign key that goes back to your USER table.

Then you can have a 1-n number of email address per user. This is generally the best practice for database normalization. If your emails have different types (ie work, personal, etc.), you could have another column in that table for the type.

---

If you need to return the rows in a single column, you could then look at using LISTAGG:

select u.id,
  listagg(ue.email, ', ') within group (order by ue.email) email_addresses
from users u
  left join user_email ue on u.id = ue.user_id
group by u.id

• SQL Fiddle Demo