Query to show actual value if count=1

From: Tom Barnes <barnest_at_san.rr.com>
Date: 14 Nov 2002 11:07:15 -0800
Message-ID: <ae6b6116.0211141107.67b634a4@posting.google.com>

We have a table that tracks the tasks employees work on every day and also the employees' locations. We need a query that summarizes for a certain time period the tasks and locations for each employee. If an employee worked on multiple tasks/locations we want the query to say 'Many', otherwise it should display the task/location.

CREATE TABLE empjobs (empid NUMBER, day DATE, jobid NUMBER, location NUMBER);

INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE),100,1000);
INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE-1),200,1000);
INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE-2),200,2000);
INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE),100,1000);
INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE-1),200,1000);
INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE-2),200,1000);
INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE),100,1000);
INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE-1),100,1000);
INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE-2),100,1000);

Desired result:
            

EMPID JOBID LOCATION
----- ----- --------

    1 Many Many
    2 Many 1000
    3 100 1000

The closest I can get is using a query like this:

SELECT empid,

	DECODE(COUNT(DISTINCT jobid),1,'One','Many') jobid,
	DECODE(COUNT(DISTINCT location),1,'One','Many') location

FROM empjobs
WHERE day BETWEEN SYSDATE-30 AND SYSDATE GROUP BY empid;

Which gives me the following result:

EMPID JOBID LOCATION
----- ----- --------

    1 Many Many
    2 Many One
    3 One One

Oracle8i Enterprise Edition Release 8.1.6.1.0

Thanks a lot,

-Tom Received on Thu Nov 14 2002 - 13:07:15 CST