CODEWITHSUNDEEP
sql-server
Amit Yadav
Amit Yadav

Reputation: 481

How to divide on column in two column in select statement in SQL Server?

I have a table named Voucher with these columns:

V_id big int,
V_Date Date,
V_Type Char(2),
Amount money,
Nature Char(10),
V_No big int,
Ledger_Id var char(20),

Now I want to generate a report named columns,

V_Date, Ledger_Id, V_No, V_Type, Credit_Amount, Debit_Amount.

where the Debit_Amount and Credit_Amount will come from the same column named Amount depending on the condition if nature = Credit then amount will go into the Credit_Amount and in the Debit it will go 0 on the same row.

And the same for the another condition if Nature = debit then Amount will go into the Debit_Amount column and in this row Credit_Amount will be 0.

How can this be done in a single query? Or do I need multiple queries for this?

Upvotes: 0

Views: 106

Answers (3)

Seeralan Saaluvar
Seeralan Saaluvar

Reputation: 11

Trying this one 

CASE 
   WHEN NATURE = 'CREDIT' 
      THEN AMOUNT 
      ELSE 0 END AS Credit_Amount, 
CASE 
   WHEN NATURE = 'DEBIT' 
      THEN AMOUNT 
      ELSE 0 END AS Debit_Amount

Upvotes: 0

Sky
Sky

Reputation: 90

You can add two if conditions with alias for your columns:

SELECT 
    *,
    IF(Nature='Credit', amount, 0) AS Credit_Amount, 
    IF(Nature='debit', amount, 0) AS Debit_Amount 
FROM <table_name>;

Though I have tried this in MYSQL, but it is very much possible in any DBMS.

Upvotes: 0

Sankar
Sankar

Reputation: 7107

Use Case when it will help you,

select 
  V_Date, 
  Ledger_Id, 
  V_No, 
  V_Type, 
  case when Nature = 'Credit' then Amount  else 0 end Credit_Amount, 
  case when Nature = 'Debit' then Amount  else 0 end Debit_Amount 
from voucher

Hope this helps...

Upvotes: 2

Related Questions