0

I need some help writing an SQL query. I am not sure how to put it in words so the best way for me to show you what I want is with an example. (I am using Oracle 10)

I have 2 tables:

Employees (2 fields): e_id e_name 1 Joe 2 Tom 3 Fred Duties (2 fields) e_id e_duty 1 'Clean Floor' 1 'Paint Walls' 2 'Lawn care' 3 'Walk Dog' 3 'Paint Fence' 3 'Cook Dinner'

The results that I'm trying to get follows:

Joe 'Clean Floor' 'Paint Walls' Tom 'Lawn Care' Fred 'Walk Dog' 'Paint Fence' 'Cook Dinner'

How can I write a query to get the desired results? Also if there is a name for this type of query, please let me know. I'm sure someone out there has already done things like this before, but I just didn't know what it was called thus not knowing what to search for.

Improve this question

1 Answer 1

Reset to default
1

If you want all the duties in separate columns this is almost impossible unless there's a known upper limit. I would use a PIVOT query if that's the case.

If you don't mind them all being in the same column you could do something like this:

select name, wm_concat(e_duty) from employees e join duties d on e.e_id = d.e_id group by name

WM_CONCAT() is not supported but is available (there are plenty of other string aggregation techniques though)

If you did have an upper limit on the number of duties you had (or wanted to return) then the pivot query would look something like this:

select e_name, "1", "2", "3", "4", "5", "6" from employees e join ( select e_id, duty , row_number() over ( partition by e_id order by 1 ) as r from duties ) d on e.e_id = d.e_id pivot ( max(duty) for r in (1, 2, 3, 4, 5, 6 ) )

The ROW_NUMBER() is there only to generate an easy key to use as the duty itself could be anything.

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

2 Comments

I actually do have an upper limit in my real world scenario. 6 is the most duties an employee can have.
In that case what I've done with the PIVOT will work; I've just updated it to be exactly the same as your requirements @user1031516.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.