SQL show multiple values from same columns

Question

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?

score 1 · Accepted Answer · 2015-07-14 11:28:14Z

1

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".

edited Jul 14, 2015 at 11:28

answered Jul 14, 2015 at 11:15

user330315

Sign up to request clarification or add additional context in comments.

Comments

Stepan Novikov · Accepted Answer · 2015-07-14 08:26:20Z

0

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

answered Jul 14, 2015 at 8:26

Stepan Novikov

1,39612 silver badges23 bronze badges

3 Comments

nowaySync Over a year ago

is not possible with simple innner join ?

Stepan Novikov Over a year ago

Nope, simple joins can return more than one row for each record in the Person table in a case if more than 1 record in table Work related to that record in a table Person by id.

Stepan Novikov Over a year ago

You can see illustration how SQL joins are working here codeproject.com/Articles/33052/…

Collectives™ on Stack Overflow

SQL show multiple values from same columns

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related