Query columns based on values of table

I’m new to DB and SQL so I don’t know if there is anything new I need to try for this. I want to solve this table my senior has given to me:

Passbook(Table Name)

Date    |  Amount  |  Type
-----------------------------
14/3/19 |   48000  |  Debit |
13/2/19 |   75000  |  Credit|
9/7/19  |   65000  |  Credit|
12/6/19 |   15000  |  Debit |

Now I have to generate a query in this manner:

Month   |  Debit   |  Credit
------------------------------
13/2/19 |     0    |  75000
14/3/19 |   48000  |    0
12/6/19 |   15000  |    0
9/7/19  |     0    |  65000

Here my Passbook table value has become the columns for query and IDK how to generate it in this manner

Anyone help me do this please

for monthly sorting, I’m supposed to use ORDER BY clause, I suppose

Now I have to generate a query in that manner.

Answer

A basic pivot query should work here:

SELECT
    Format(Month([Date])) AS Month,
    SUM(IIF(Type = 'Debit', Amount, 0)) AS Debit,
    SUM(IIF(Type = 'Credit', Amount, 0)) AS Credit
FROM yourTable
GROUP BY
    Format(Month([Date]));

If you instead want date level output, then aggregate the by the Date column directly.

Advertisement

Answer