How to see the duplicates in a different columns in Oracle SQL?

Question

I try to find the duplicates but at the same time be able to see it in a different columns but this duplicates is in the same report and also I verified the duplicates by year.

report	id_tip	date	duration	source	tip	cnt
1234	1111212	31/12/22	9	S04	BS	2
3456	2233242	20/10/22	80	K34	LS	2

And this is the query I did in Oracle SQL Developer:

SELECT *
FROM   (
        SELECT ID_PPAL_ICI AS report,
               BLNC_ID AS id_tip,
               BLNC_FEC_CIERRE AS date,
               BLNC_DUR AS duration,
               ID_PPAL_FNT AS source,
               BLNC_TIP AS tip,
               COUNT(*) OVER ( PARTITION BY ID_PPAL_ICI, EXTRACT(YEAR FROM BLNC_FEC_CIERRE)) AS cnt
                 
FROM   BLNC_BALANCES
  
    INNER JOIN ID_PPAL
    ON BLNC_ID_PPAL_ICI = ID_PPAL_ICI
         
ORDER BY BLNC_FEC_CIERRE DESC
)
WHERE  cnt > 1;

select * from BLNC_BALANCES;

But I would like to see a table like this:

report	id_tip	date	duration	source	tip	id_tip_1	date_1	duration_1	source_1	tip_1
1234	1111212	31/12/22	9	S04	BS	1122215	24/03/22	1	S02	BS
3456	2233242	20/10/22	80	K34	LS	4455385	15/02/22	11	H12	NS

I've already try with a left join and HAVING COUNT but all I have is erros. Any help? please

Note: The amount of duplicates per row is at most one.

Answer 1

You can self-join on mismatching dates, while keeping the partitioning of duplicates intact, by matching:

• "report" values
• year of "date" values

WITH cte AS (
    SELECT ID_PPAL_ICI       AS report,
           BLNC_ID           AS id_tip,
           BLNC_FEC_CIERRE   AS date,
           BLNC_DUR          AS duration,
           ID_PPAL_FNT       AS source,
           BLNC_TIP          AS tip,
    FROM BLNC_BALANCES
    INNER JOIN ID_PPAL ON BLNC_ID_PPAL_ICI = ID_PPAL_ICI
)
SELECT t1.*,
       t2.id_tip   AS id_tip_1,
       t2.date     AS date_1,
       t2.duration AS duration_1,
       t2.source   AS source_1,
       t2.tip      AS tip_1
FROM       cte t1
INNER JOIN cte t2
        ON t1.report = t2.report 
       AND EXTRACT(YEAR FROM t1.date) = EXTRACT(YEAR FROM t2.date)
       AND NOT t1.date = t2.date