0

I am a beginner in SQL so I don't know how to approach this problem. It's Northwind related, done with Oracle. I have a table of employees, and each employee has another employee's ID as a Manager (i.e the person the employee reports to):

I need to flip it around so that instead of an employee having a manager, the manager should have a list of subordinates in a single cell.

How should I approach this? I have no idea...

EpicCheese
  • 35
  • 4
  • subordinates are only N-1 subordinates or also N-2, N-3 etc... ? – Jean-Christophe Blanchard Mar 31 '16 at 19:47
  • 1
    You need this to be the result of your query? Or you need this to be the design of your table schema? If the latter, you should abandon that idea and create a subordinates table instead. – squillman Mar 31 '16 at 19:48
  • what is the structure of the employee table ? – Jean-Christophe Blanchard Mar 31 '16 at 19:50
  • @squillman It should be the result of a query, and the table can't be edited in any way. It's an exercise for a database course. – EpicCheese Mar 31 '16 at 19:52
  • @Jean-Christophe Blanchard There's info about the database and it's tables at Northwind's homepage at https://northwinddatabase.codeplex.com/ – EpicCheese Mar 31 '16 at 19:56
  • I think you should start by knowing where is stored the manager id, I can't see it in the model. – Jean-Christophe Blanchard Mar 31 '16 at 20:05
  • @Jean-Christophe Blanchard Ah, I'm sorry. It's the REPORTSTO column. I remembered its name wrong in my OP. – EpicCheese Mar 31 '16 at 20:10
  • 1
    Northwind is the SQL Server sample database, not Oracle. Why would you be using that schema in an Oracle database? Are you sure you are using Oracle and not SQL Server? Or are you maybe using Oracle with the `HR` schema rather than Northwind? Fundamentally, it also doesn't make sense to store multiple values in a single cell-- that violates every rule of proper normalization. – Justin Cave Mar 31 '16 at 20:30

2 Answers2

0

I think to list all the subordinate you should use a hierarchical query :

select *
from
employees
start with NAME = <NAME>
connect by REPORTSTO = prior EMPLOYEE_ID ;

you have example here too Oracle Hierarchical Query: Find entire tree from the root giving a any node

NB Putting the list in one cell it's the step after.

edit reverted employee_id in connect by

Community
  • 1
  • 1
Jean-Christophe Blanchard
  • 438
  • 3
  • 11
0

You could concatenate them into a cell but I am unsure as to if that is what you want or what use data in that form would be. You want data to be in separate cells from each other.

Here is also an example of an oracle solution. https://stackoverflow.com/a/9621167

Community
  • 1
  • 1
bfs
  • 192
  • 2
  • 7