You obviously didn't hear that from me. 😉 There's absolutely no reason why this can't be done in T-SQL and it's one less piece of managed code that you would have to resource, recompile, and repromote if you needed to make a change. Think of it as easy encapsulation. 😉

First, you took more time trying to format your request than doing it the right way. If you had done it the right way, it wouldn't have deterred someone like me from actually giving it try for almost a week. The "Right Way" is documented in the in the first link in my signature line below under "Useful Links".

I'll do it for you this time only because you've been misled by someone into thinking that doing it in T-SQL is "Not Recommended". Yeah, there's a whole lot of people that will say that. I just don't happen to agree with them for the reasons previously mentioned.

Here's the test table setup if anyone else wants to play with some of the more advanced features of GROUP BY that came out in 2008. I kept this one at a 2005 (or up) level because it's a common question across all versions.

SELECT *

INTO #TestTable

FROM (

SELECT 'V001',100, 10,1000 UNION ALL

SELECT 'V001', 10, 20, 200 UNION ALL

SELECT 'V222', 20, 5, 100 UNION ALL

SELECT 'V222', 5,100, 500

)d(VendorCode,UnitPrice,Qty,Total)

;

See? Not much more difficult than the formatting you tried to do.

Also, you wrote that you wanted sub-totals of the Unit Prices and the Qty's. Unless the parts (or whatever) are all identical, it makes no sense to sub-total or total those columns. With that in mind, I've written a CASE statement into the code for those two columns to output just a blank for sub-totals. That's easy to change, of course.

On to the code. The following produces the desired output (without the subtotals for 2 columns as per above). I personally don't believe you need the ORDER BY for this one but a lot of people get all bent out of shape if it's not there. Since it doesn't hurt anything by being there, I've included it.

WITH

cteEnumerate AS

(

SELECT RowNum = ROW_NUMBER() OVER(ORDER BY VendorCode)

,VendorCode,UnitPrice,Qty,Total

FROM #TestTable

)

SELECT VendorCode = CASE

WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 0 THEN VendorCode

WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 1 THEN 'Sub Total'

WHEN GROUPING(VendorCode) = 1 AND GROUPING(RowNum) = 1 THEN 'Grand Total'

END

,UnitPrice = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(UnitPrice) AS VARCHAR(10)) ELSE '' END

,Qty = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(Qty) AS VARCHAR(10)) ELSE '' END

,Total = SUM(Total)

--,MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum) --Uncomment this line to see how it all works

FROM cteEnumerate

GROUP BY VendorCode,RowNum WITH ROLLUP

ORDER BY MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum)

;

Here's the output from the above...

VendorCode UnitPrice Qty Total

----------- ---------- ---------- -----------

V001 100 10 1000

V001 10 20 200

Sub Total 1200

V222 20 5 100

V222 5 100 500

Sub Total 600

Grand Total 1800

(7 row(s) affected)

I'd write a full blown explanation of the code for you, but you have to have some of the fun. 😉 I strongly recommend you lookup the things I used in Books Online so that you're not not so easily lured into believing in someone's recommendation that "it's not recommended" .