Title: query that is splitting a column into two
Tags: sql;sql-server
Question: 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

Here is the accepted answer: You can use a case statement to find which column the amount belongs in:
SELECT  id ,
CASE WHEN amount &gt;= 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.

Here is another answer: Select ID, Amount as Debit, null as Credit
From Account
Where Amount &gt;= 0
Union All
Select ID, null as Debit, Amount as Credit
From Account
Where Amount < 0
Comment for this answer: Fix name of column in where, and I think is better to use it without cast and replace `'null'` with `null`. Like this http://sqlfiddle.com/#!6/984f7/3
Comment for this answer: to be honest I was being a bit pedantic, I don't like seeing the value null in results!

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/28/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sql-server>', 'Title': 'query that is splitting a column into two', 'ViewCount': 555, 'id': 27195546, 'user_AboutMe': '', 'user_AccountId': 3972876.0, 'user_DisplayName': 'Master', 'user_Reputation': 1891.0, 'user_Views': 310.0, 'user_id': 3276954.0}}