[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.
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[
^]
Submit an article or tip