CODEWITHSUNDEEP
sqloracle-database
nowaySync
nowaySync

Reputation: 115

SQL show multiple values from same columns

There are 2 tables and I must do an inner join.

First table called People 

Name, Surname, id, and value

Second table called Work 

id (external key of fist table), category, state, roles, date.

Column "Roles" can have multiple values (employee director workers etc).

I must show with inner join the history of roles for each people in one row ( Name Surname roles1, roles 2 roles3)

Example Jack Brown employee director workers

How can I show in one row multiple values contained in one Columns?

Upvotes: 1

Views: 159

Answers (2)

user330315
user330315

Reputation:

If you just need to see the roles but don't really require them to be in separate columns you can use listagg()

select p.id, 
       p.name, 
       p.surname, 
       listagg(w.roles, ',') within group (order by start_date) as all_rows
from people p
  join work w on p.id = w.id
group by p.id, p.name, p.surname

This would output something like this:

ID | NAME   | SURNAME | ALL_ROLES                
---+--------+---------+-------------------------
 1 | Jack   | Brown   | employee,worker,director

You can't actually have each role in a separate column, because in SQL the number of columns in a result is fixed. So you can't have a result that has three columns for the roles of "Jack Brown" and two column for the roles of "Arthur Dent".

Upvotes: 1

Stepan Novikov
Stepan Novikov

Reputation: 1396

You could write PL-SQL function which will select all related records from table Work by given id from table People and iterate it in a cursor to build a string with all of roles or you could generate XML by using of DBMS_XMLGEN.GETXML function if you are using the 10g version of Oracle or higher

Upvotes: 0

Related Questions