Query dates from different rows in the same table to appear on the same row in the query

I have seen many posts about this issue using joins but not for the same table. I am trying to compare the dates from different rows on the same table. I am writing tax software that tracks liens. The tax charge is on one line and any interest charged is on another. I need to compare the dates of the tax line and the dates of the interest line here is the query I tried

SELECT 
    ID,
    TAX_YEAR, 
    (SELECT TAX5_DATE FROM Tran_File WHERE TRAN_CODE = '878LOT' AND TAX5_DATE IS NOT NULL) AS TaxLineLienDate,
    (SELECT TAX5_DATE FROM Tran_File WHERE TRAN_CODE = '878LOI' AND TAX5_DATE IS NOT NULL) AS InterestLineLienDate
FROM
    Tran_File

WHERE 
    TAX5_DATE IS NOT NULL
    AND (TRAN_CODE = '878LOT' OR TRAN_CODE = '878LOI')

The sub query is returning multiple records. I want the TaxLineLienDate and InterestLineLienDate on the same line of the query for each ID. Any help would be appreciated. I am using SQL Server.

Answer

If I’m understanding correctly, you can use conditional aggregation for this:

select id, 
    tax_year, 
    max(case when TRAN_CODE = '878LOT' then TAX5_DATE end) TaxLineLienDate,
    max(case when TRAN_CODE = '878LOI' then TAX5_DATE end) InterestLineLienDate
from Tran_File
where TAX5_DATE is not null
    and TRAN_CODE in ('878LOT', '878LOI')
group by id, tax_year