Query SQL Return Results to Excel & Format

Question

Essentially this is what I need...if you have any follow-up ?'s or need any further info please ask...

1st row of each section would be headers

Query SQL Table

Return results under headers

Total the $ column

space

headers

Return results

Total the $column

space

(if bottom of set)

Grand Total & A total of all the totalcolumns

SalesRep ItemSold Quantity Price

JoeName 40' tv 3 $500

JoeName 20' tv 10 $200

Total $700

SaleRep ItemSold Quantity Price

Rname 10' TV 200 $100

Rname 30' TV 75 $300

Total $400

Grand Total $1100

How would I do that in C#?

Jignesh Trivedi · Answer

hi try this public class Sales { public string SalesRep { get; set; } public string ItemSold { get; set; } public int Quantity { get; set; } public int Price { get; set; } } static void Main(string[] args) { List sales = new List (); sales.Add(new Sales { SalesRep = JoeName ItemSold = 40' tv Quantity = 3 Price = 500 }); sales.Add(new Sales { SalesRep = JoeName ItemSold = 20' tv Quantity = 10 Price = 200 }); sales.Add(new Sales { SalesRep = Rname ItemSold = 10' tv Quantity = 200 Price = 100 }); sales.Add(new Sales { SalesRep = Rname ItemSold = 30' tv Quantity = 75 Price = 300 }); var disvalue = from s in sales group s by s.SalesRep into grpsales select new Sales { SalesRep = grpsales.Key Price = grpsales.Sum(da => da.Price) }; foreach (var value in disvalue) { var other = from s in sales where s.SalesRep == value.SalesRep select s; if (other.Count() > 0) { Console.WriteLine(SaleRep	ItemSold	Quantity	Price); foreach (var newval in other) { Console.WriteLine(newval.SalesRep + 	 + newval.ItemSold + 	 + newval.Quantity + 	 + newval.Price); } Console.WriteLine(Total		 + value.Price.ToString()); } } Console.Read(); } you can achive this using RollUp in SQL server. Please refer http://wikidba.wordpress.com/tag/header-for-rollup/ http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/ hope this will help you.