Change output SQL-CodePudding

I want to change the output from this query where the output for Debit_Credit would be 1 and 2 which is 1 for Debit, 2 for Credit. But the output will be display in one column.

select t_ocmp as Company, t_otyp as TransactionType, t_odoc as DocumentNo
    , t_leac as LedgerAcc, t_fyer as FiscalYear, t_fprd as FiscalPeriod, t_obat as Batch
    , t_dcdt as DocumentDate, t_bpid as BusinessPartner, t_refr as Reference
    , t_ccty as Tax_Country, t_cvat as Tax_Code, t_ccur as Currency
    , t_amnt as Amount_Foreign, t_amth_1 as Amount_Home_Currency
    , t_dbcr as Debit_Credit
from ....
where t_fprd=10 and t_fyer=2021 and t_leac=11041010;

the output will get like this for debit_credit column

enter image description here

How to change from 1,2 output into Debit,Credit output?

Thanks.

CodePudding user response：

I think you can use case for the different alias for the same column

SELECT t_ocmp AS Company, t_otyp AS TransactionType, t_odoc AS DocumentNo
    , t_leac AS LedgerAcc, t_fyer AS FiscalYear, t_fprd AS FiscalPeriod
    , t_obat AS Batch, t_dcdt AS DocumentDate, t_bpid AS BusinessPartner
    , t_refr AS Reference, t_ccty AS Tax_Country, t_cvat AS Tax_Code
    , t_ccur AS Currency, t_amnt AS Amount_Foreign, t_amth_1 AS Amount_Home_Currency 
    , CASE WHEN t_dbcr=1 THEN t_dbcr ELSE 0 END AS credit
    , CASE WHEN t_dbcr= 2 THEN t_dbcr ELSE 0 END AS debit
FROM ...
WHERE t_fprd = 10 AND t_fyer = 2021 AND t_leac = 11041010;

CodePudding user response：

Kindly check.

select t_ocmp as Company, t_otyp as TransactionType, t_odoc as DocumentNo
    , t_leac as LedgerAcc, t_fyer as FiscalYear, t_fprd as FiscalPeriod, t_obat as Batch
    , t_dcdt as DocumentDate, t_bpid as BusinessPartner, t_refr as Reference
    , t_ccty as Tax_Country, t_cvat as Tax_Code, t_ccur as Currency
    , t_amnt as Amount_Foreign, t_amth_1 as Amount_Home_Currency
    , case when t_dbcr= 1 then 'Debit' when t_dbcr=2 then 'Credit' end as Debit_Credit
from ....
where t_fprd=10 and t_fyer=2021 and t_leac=11041010;