groupby to check each of their date in oracle sql

I have a table ISIN_TBL which has two fields IDENTIFIER and ALLT_DATE which are of datatype varchar and Date respectively.

There are many ALLT_DATE for each IDENTIFIER in the table. Normally for each IDENTIFIER the ALLT_DATE should be the same. But just to confirm i want to write a sql query to list IDENTIFIER which has has different ALLT_DATE.

I want to modify my below sql query:

Select count(*), IDENTIFIER, ALLT_DATE 
from ISIN_TBL
group by IDENTIFIER, ALLT_DATE

Answer

You can use GROUP BY and HAVING to get the exceptsion

Select IDENTIFIER 
from ISIN_TBL
group by IDENTIFIER
having MIN(ALLT_DATE) <> MAX(ALLT_DATE);

If you want the list of dates — and it is not too long — you can use LISTAGG():

Select IDENTIFIER, LISTAGG(ALLT_DATE, ',') WITHIN GROUP (ORDER BY ALLT_DATE) 
from ISIN_TBL
group by IDENTIFIER
having MIN(ALLT_DATE) <> MAX(ALLT_DATE);

Or if there are lots of rows, uses SELECT DISTINCT to get distinct values:

Select IDENTIFIER, LISTAGG(ALLT_DATE, ',') WITHIN GROUP (ORDER BY ALLT_DATE) 
from (SELECT DISTINCT IDENTIFIER, ALLT_DATE
      FROM ISIN_TBL
     ) t
group by IDENTIFIER
having MIN(ALLT_DATE) <> MAX(ALLT_DATE);