Click here to Skip to main content
15,030,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I could not explain my problem in my last question so I am posting a fresh question.


I have a table:

ename | edate
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 02-10-17
emp2 | 01-10-17
emp3 | 01-10-17
emp3 | 01-10-17
emp3 | 02-10-17
emp3 | 02-10-17


Expected output after pivoting:
ename | 01-10-17 | 02-10-17
emp1 | 3 | 1
emp2 | 1 | 0
emp3 | 2 | 2

and there is condition that count for each day should be greater than 1.

So final out put is
ename | 01-10-17 | 02-10-17
emp3 | 2 | 2

as for only emp 3 count for all days is more than 1.

I have used pivot in oracle 11g. But the actually this query will run on 10g and I found out that pivot is not supported in 10g.

What I have tried:

In oracle 11g,

SELECT * FROM
(
SELECT ename, edate
FROM tblName
)
PIVOT
(
COUNT(edate)
FOR edate in('01-oct-2017', '02-oct-2017')
)
ORDER BY ename;

But I cannot find way to add the "> 1" condition and also now I have typed the dates manually but in real situation they can be any multiple dates.
Posted
Updated 12-Oct-17 3:46am

1 solution

[EDIT]
If you would like to get only these data where count is bigger than 1, you can use HAVING clause[^] to limit result set as a source of pivot.
SQL
SELECT ename, edate, COUNT(*) AS CountOfDates
FROM tblName
GROUP BY ename, edate
HAVING COUNT(*)>1


As to your second question: there's a way to create pivot table without typing dates: Dynamic pivot in oracle sql - Stack Overflow[^]
So, i would try (but i'm not having installed Oracle server) to improve referenced sql statements:
clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || edate || ''' AS ' || edate,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT edate FROM tblName);

--this should put a list of dates in format: <code>'01-Oct-2017' as 01-Oct-2017</code> and so on...  into a variable

SELECT *
FROM (
    SELECT ename, edate, COUNT(*) AS CountOfDates
    FROM tblName
    GROUP BY ename, edate
    HAVING COUNT(*)>1
)
PIVOT (Sum(CountOfDates) AS val FOR edate IN (&str_in_statement));


Note: There are limitations though: You can only concatenate a string up to 4000 bytes.

For further details, please see:
Oracle SQL*PLUS new_value parameter[^]
Command Reference, 14 of 52[^]
   
v2
Comments
planetz 13-Oct-17 1:10am
   
I have to show individual dates as individual column names and their respective count under them.
Maciej Los 13-Oct-17 3:19am
   
Check improved answer.
planetz 13-Oct-17 6:27am
   
'having' clause surely is a condition but it wont check for everyday when they are placed one beside other. For example, if for each day count >1 then output should only have

ename | 01-10-17 | 02-10-17
emp3 | 2 | 2

But with your query it comes as:

ename | 01-10-17 | 02-10-17
emp3 | 2 | 2
emp1 | 3 | null
Maciej Los 13-Oct-17 9:20am
   
So what? You need to add another condition. Think of it and try.
If my answer was helpful, please, accept it (green button).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900