Total field in Crosstab query in SQL Server 2008

Question

I have got this result with the answer given by bluefeet

Equipt  BSL    AQ
TFP     3      2
TM      1      0
VCB     18     6
VCD     5      8

Query script was

 SELECT  Equipt, [BSL] AS BSL, [AQ] AS AQ 
 FROM 
 ( 
 SELECT Equipt, Shed 
 FROM PunctualityMain WHERE Date >= '4/1/2012' AND Date <= '4/30/2012' AND classification    = 'Loco' 
) x
PIVOT 
( 
COUNT(Shed) 
FOR Shed IN ([BSL], [AQ])
)  p 

Can it be possible to added one total field the above script like access crosstab

Equipt  BSL    AQ     TTL
TFP     3      2     5
TM      1      0     1
VCB     18     6     24
VCD     5      8     13

Answer 1

You should be able to add the Totals field by including a new column:

SELECT  Equipt, [BSL] AS BSL, [AQ] AS AQ, ([BSL] + [AQ]) as TTL
FROM 
( 
     SELECT Equipt, Shed 
     FROM PunctualityMain 
     WHERE Date >= '4/1/2012' AND Date <= '4/30/2012' 
          AND classification    = 'Loco' 
) x
PIVOT 
( 
    COUNT(Shed) 
    FOR Shed IN ([BSL], [AQ])
)  p