Oracle SQL: Display single columns from multiple rows of a single table with Logic-CodePudding

Oracle SQL

I would like to look-up data from 2 rows of the same column from the same table together in an existing long query with multiple joins.

Current table set-up (single table):

Table: ACCOUNT_DETAILS
| TRX_ID | TYPE | FAC_ID | ACC_ID |
| ------ | ---- | ------ | ------ |
| 1234   | CRDR | ABC123 | AB1234 |
| 1234   | DBTR | XYZ222 | XY9800 |
| 9876   | CRDR | EFG999 | EF7659 |
| 9876   | DBTR | ABC123 | AB9900 |

Expected Result:

Table: REPORT
| TRX_ID | Counterparty FAC_ID | Counterparty ACC_ID |
| ------ | ------------------- | ------------------- |
| 1234   | XYZ222              | XY9800              |
| 9876   | EFG999              | EF7659              |

Logic needed: If FAC_ID NOT LIKE 'ABC%' then refer to the Counterparty FAC_ID (e.g. for TRX_ID = 1234, it will refer to the DBTR FAC_ID and DBTR ACC_ID; TRX_ID = 9876, it will refer to the CRDR FAC_ID AND CRDR ACC_ID)

Example:

SELECT (CASE WHEN TYPE = 'DBTR' AND FAC_ID LIKE 'ABC%' THEN (SELECT FAC_ID FROM ACCOUNT_DETAILS WHERE TYPE = 'CRDR') 
ELSE (SELECT FAC_ID FROM ACCOUNT_DETAILS WHERE TYPE = 'DBTR') END)
FROM ACCOUNT_DETAILS

I've tried options such as JOINs, UNIONs and subqueries but it does not work. I would like to have the Counterparty FAC_ID and Counterparty ACC_IDs in separate single lines in the query, as I will include it in a long query that I already have.

CodePudding user response：

We can use CASE in a CTE to flag the rows where FAC_ID does not start with "ABC" and then use the flag in the WHERE clause.
See db<>fiddle here for schema.

with cte as 
(
select 
TRX_ID,
FAC_ID,
ACC_ID,
CASE WHEN FAC_ID LIKE 'ABC%' THEN 0 ELSE 1 END ordinal
FROM ACCOUNT_DETAILS)
SELECT
TRX_ID,
FAC_ID,
ACC_ID
FROM CTE
WHERE ordinal = 1;

TRX_ID | FAC_ID | ACC_ID
-----: | :----- | :-----
  1234 | XYZ222 | XY9800
  9876 | EFG999 | EF7659

db<>fiddle here