i found its not recommended so i have requested to handle it in the front end .
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 '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
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.
SELECT RowNum = ROW_NUMBER() OVER(ORDER BY VendorCode)
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'
,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
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" .
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs