Sql Query Solution

I have a query as follows.

select strftime('%Y-%m',A.traDate) as Month,sum(A.TraAmt) as Total,C.GroupType from 
TransactionTbl A left join TransactionCategory B on A.CategoryID = B.CategoryID left join 
CategoryGroup C on B.CatGRoupID=C.CatGRoupID where A.UserID=1 and A.ProfileID=1 and 
date(A.TraDate) between date('2009-12-01') and date('2010-11-01') group by C.GroupType,  
strftime('%m',A.traDate) order by Month

Above query gives result as follows,

Month Total C.GroupType 
----- ----- ----------- 
2009-12 4100 Expense 
2009-12 8000 Income 
2010-01 200     Expense 
2010-01    2000    Income
2010-02    3500    Expense
2010-02    7500    Income

Oke, & I want solution like this.

    Month Income Expense 
    ----- ----- ----------- 
    2009-12 8000    4100
    2010-01 8000    200
    2010-02 7500    3500

I am trying hard to get output like this, but I could find any kind of solution.

Would some one help me please?

I am an iPhone developer & I know a little about sql queries.

Thanks in advance for sharing your knowledge.

Sagar

From stackoverflow

• This is one way of achieving what you want:

  SELECT
      T1.Month,
      SUM(CASE T1.GROUPTYPE WHEN 'Income' THEN T1.TOTAL ELSE 0 END) AS Income,
      SUM(CASE T1.GROUPTYPE WHEN 'Expense' THEN T1.TOTAL ELSE 0 END) AS Expense
  FROM (/* Your query here */) AS T1
  GROUP BY T1.Month
  

  Mark Byers : I have tested it now, and there were two small errors which I have now fixed (answer is updated). One was a comma before the FROM which shouldn't have been there, and the other was the missing END keyword in two places. Try it again now, and hopefully it should work. Note that I have tested this on MSSQL server. I assume that SQLite supports this syntax too, but haven't tested it on SQLite.

  ScSub : sagar, you are saying you managed to use case/when/then/else/end in sqlite? can you post your 'create view' definition here?

  sugar : No, Sorry. It was my mistake. Solution is correct as given in this answer & I am not following any view @ScSub.