Hey,

I have the following SQL query:

Code:
DECLARE @SupplierList varchar(max), @FromDate smalldatetime, @ToDate smalldatetime, @CompanyList varchar(max)

DECLARE	@Results TABLE (
		SupplierType nvarchar(255),
		SupplierName nvarchar(255),
		PackageID int,
		PackageDepartureDate smalldatetime,
		Nights int,
		BudgetCost numeric(9,2),
		ConfirmedCost numeric(9,2)
)

INSERT INTO @Results (
		SupplierType,
		SupplierName,
		PackageID,
		PackageDepartureDate,
		Nights,
		BudgetCost,
		ConfirmedCost)

SELECT  L.Name AS SupplierType,
        S.Name AS SupplierName, 
        Q.ID AS PackageID,
        Q.Depart AS PackageDepartureDate,
        Q.Nights AS Nights,
        ISNULL(I.Budget,0) AS #2BudgetCost, 
        ISNULL(I.Actual,0) AS #2ConfirmedCost
FROM	Supplier S 
INNER JOIN Itinerary I ON I.SupplierID = S.ID 
INNER JOIN Lookup L ON L.ID = S.TID 
LEFT OUTER JOIN Quote Q ON Q.ID = I.QuoteID
LEFT OUTER JOIN Company C ON C.ID = Q.CompanyID
WHERE (Q.ID IS NOT NULL)
GROUP BY L.Name, S.Name, Q.ID, Q.Depart, Q.Nights, I.Budget, I.Actual
ORDER BY L.Name, S.Name, Q.ID, Q.Depart, Q.Nights, I.Budget, I.Actual

SELECT  SupplierType,
		SupplierName,
		PackageID,
		PackageDepartureDate,
		Nights,
		BudgetCost,
		ConfirmedCost
FROM @Results
This returns results like so:

Rail Switzerland Travel Centre ( UK)) 90756 2011-09-11 00:00:00 5 0.00 0.00
Rail Switzerland Travel Centre ( UK)) 90756 2011-09-11 00:00:00 5 48.00 0.00
Rail Switzerland Travel Centre ( UK)) 90756 2011-09-11 00:00:00 5 67.50 0.00
Sundry Item Special Occasion Packages 82297 2009-12-29 00:00:00 4 0.00 0.00
Sundry Item Special Occasion Packages 82318 2009-10-09 00:00:00 9 0.00 0.00
Sundry Item Special Occasion Packages 85788 2010-03-20 00:00:00 6 0.00 0.00
Sundry Item Special Occasion Packages 85789 2010-03-26 00:00:00 6 0.00 0.00
So you see i have Supplier Types such as "Rail" and "Sundry Item", then there are Supplier Names such as "Switzerland Travel Centre" and "Special Occasion Packages".

Now what i need to do is add an additional row underneath the end of a supplier name and add the columns BudgetCost and ConfirmedCost..

Is this possible? I'm not sure how i can go about doing this..

So for example when the "Switzerland Travel Centre" supplier name is shown add a subtotal column to add up the values specified above.

Something like this i was thinking:

Code:
SELECT	'<B>Supplier Total:' SupplierType,
		'' SupplierName,
		'' PackageID,
		'' PackageDepartureDate,
		'' Nights,
		Sum(BudgetCost) BudgetCost,
		Sum(ConfirmedCost) ConfirmedCost
FROM	@Results
That would show a total of ALL the BudgetCost and ConfirmedCost, but i need this to show IN the actual SQL results for EACH supplier.

I hope someone can help,

Many thanks,