sql select tuples and group by id

Question

I have the current database schema

 EMPLOYEES
 ID | NAME | JOB

 JOBS
 ID | JOBNAME | PRICE

I want to query so that it goes through each employee, and gets all their jobs, but I want each employee ID to be grouped so that it returns the employee ID followed by all the jobs they have. e.g if employee with ID 1 had jobs with ID, JOBNAME (1, Roofing), (1,Brick laying) I want it to return something like

1 Roofing Bricklaying

I was trying

SELECT ID,JOBNAME FROM JOBS WHERE ID IN (SELECT ID FROM EMPLOYEES) GROUP BY ID;

but get the error

not a GROUP BY expression

Hope this is clear enough, if not please say and I'll try to explain better

What version of Oracle are you using? Do you want your result to have 2 columns (so "Roofing Bricklaying" becomes a combined string)? Or do you want your result to have 3 columns (so "Roofing" and "Bricklaying" are separate columns)? Do you know at most how many jobs one person can have (and thus how many columns you want the result set to have)? — Justin Cave, Dec 01 '14 at 20:22
Oracle version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Can be either 2 or 3 columns, whichever is possible as long as ID and JOBS are in different columns and there is no upper limit on the number of jobs — Summer, Dec 01 '14 at 20:26
Take a look at [sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) — iris, Dec 01 '14 at 20:35

score 0 · Answer 1 · answered Dec 01 '14 at 20:30

0

You need to put JobName to group by expression too.

 SELECT ID,JOBNAME FROM JOBS WHERE ID IN (SELECT ID FROM EMPLOYEES) GROUP BY ID,JOBNAME;

answered Dec 01 '14 at 20:30

Atilla Ozgur

14,339
3
49
69

This returns a new row for each ID though? As it returns 1 | Roofing 1 | Brick Laying – Summer Dec 01 '14 at 20:33

Spider · Accepted Answer · 2014-12-02T13:38:03.517

0

EDIT:

WITH ALL_JOBS AS
    (
    SELECT ID,LISTAGG(JOBNAME || ' ') WITHIN GROUP (ORDER BY ID) JOBNAMES FROM JOBS GROUP BY ID
    )
    SELECT ID,JOBNAMES FROM ALL_JOBS A,EMPLOYEES B
    WHERE A.ID = B.ID
    GROUP BY ID,JOBNAMES;

In the with clause, I am grouping by on ID and concatenating the columns corresponding to an ID(also concatenating with ' ' to distinguish the columns). For example, if we have

ID NAME
1  Roofing
1  Brick laying
2  Michael
2  Schumacher

we will get the result set as

ID NAME
1  Roofing Brick laying
2  Michael Schumacher

Then, I am join this result set with the EMPLOYEES table on ID.

edited Dec 02 '14 at 13:38

answered Dec 01 '14 at 21:00

Spider

367
3
15

Nice, but would it be possible to add a couple of words about the key principle – Christophe Dec 01 '14 at 22:35
Hiya, this may well solve the problem... but it'd be good if you could edit your answer and provide a little explanation about how and why it works :) Don't forget - there are heaps of newbies on Stack overflow, and they could learn a thing or two from your expertise - what's obvious to you might not be so to them. – Taryn East Dec 01 '14 at 22:42

sql select tuples and group by id

2 Answers2