pivot difference

[Dennis Chou]

i have a table

broker stock shares
ubs ibm 100
ubs ibm 200
ms ibm 200

how can i get the following?

stock ubs ms difference
ibm 300 200 100

thanks

dennis

 

[KARL DEWEY]

This will work if you only have 2 brokers --
Dennis_2 --
SELECT Dennis.broker, Count(1) AS Broker_Num
FROM Dennis
GROUP BY Dennis.broker;

TRANSFORM Sum(Dennis.shares) AS SumOfshares
SELECT Dennis.stock, Sum(Dennis.shares) AS [Total Of shares],
Abs(Sum(IIf([Broker_Num]=1,[shares],0))-Sum(IIf([Broker_Num]=2,[shares],0)))
AS Difference
FROM Dennis INNER JOIN Dennis_2 ON Dennis.broker = Dennis_2.broker
GROUP BY Dennis.stock
PIVOT Dennis.broker;