Calculated Fields Question

[junkjones]

I have a query that is driving me bananas!

The table (which was designed poorly) has multiple records for transactions, with different months in each record:

DATE TRANSACTION VOLUME
-----------------------------
March 1 62
April 1 23
May 1 43
March 2 42
April 2 25

I want to output it like this:

TRANSACTION MARCH APRIL MAY
-----------------------------
1 62 23 43
2 42 25

Here is my query so far (that doesn't work):

SELECT transaction, date,
(SELECT MAX(volume) FROM table
WHERE [long/short] = table.[long/short]
AND temp_ID = table.temp_ID
AND company_ID = 'ABC'
AND month = 'April')
AS GJ_end_April

from table
where company_ID = 'ABC'
AND month = 'April'
group by transaction


The problem is that it grabs the largest value for all april volumes, and returns that volume for every row. How do I get it to pull it for each transaction? I thought the:

WHERE [long/short] = table.[long/short]
AND temp_ID = table.temp_ID

would be what told it that, but the query results don't change whether those lines are there or not

 

[ClaireHCM]

try this

select trans,
max(case when trans = 1 and date='march' then vol when trans =2 and date='march' then vol else 0 end) March,
max(case when trans = 1 and date='April' then vol when trans =2 and date='April' then vol else 0 end) April,
max(case when trans = 1 and date='May' then vol when trans =2 and date='May' then vol else 0 end) May
from mytbale group by trans