CODEWITHSUNDEEP
sqlsql-server
Master
Master

Reputation: 2153

query that is splitting a column into two

Hello I have an ID column and an amount column at the moment. A value is represented as a Debit if the amount is positive. A credit if the amount is negative. I'm wondering how can I "Split" my amount column.

Select * from Test.dbo.Accounts

Produces

ID  | Amount
 1  |   500
 2  |  -600

So Item 1 is a Debit, Item two is a credit. I want to query the Database so that it displays as followed

ID  | Debit  | Credit
 1  | 500    | null
 2  | null   |-600

Upvotes: 0

Views: 795

Answers (2)

kbbucks
kbbucks

Reputation: 138

Select ID, Amount as Debit, null as Credit
From Account
Where Amount >= 0

Union All

Select ID, null as Debit, Amount as Credit
From Account
Where Amount < 0

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

You can use a case statement to find which column the amount belongs in:

SELECT  id ,
        CASE WHEN amount >= 0 THEN amount
             ELSE NULL
        END AS debit ,
        CASE WHEN amount < 0 THEN amount
             ELSE NULL
        END AS credit
FROM    Test.dbo.Accounts

I assumed 0 should go in debits but that'd be your call.

Upvotes: 4

Related Questions