I am trying to work up a general ledger appearance in an asp.net page. Here's the data i have so far.
AccountName Desc Type Amount
-----------------------------------------
Account1 Stuff Debit 5000
Account1 Stuff Credit 4000
Account2 Other Debit 3000
Account2 Other Credit 6000
Account3 Other Debit 7000
Account3 Other Credit 2000
Account4 Stuff Debit 4000
Account4 Stuff Credit 3000
Account5 Other Debit 4000
Account5 Other Credit 8000
I could also query it as this
AccountName Desc Debit Credit
-------------------------------------
Account1 Stuff 5000 4000
Account2 Other 3000 6000
Account3 Other 7000 2000
Account4 Stuff 4000 3000
Account5 Other 4000 8000
What i want is to display it in an aspx page (preferrably in a datagrid so i can export to excel) looking like this
AccountName Stuff Other
Debit Credit Debit Credit
---------------------------------------------
Account1 5000 4000
Account2 3000 6000
Account3 7000 2000
Account4 4000 3000
Account5 4000 8000
---------------------------------------------
9000 7000 14000 16000
2000 -2000
Is this even doable in a datagrid? I was thinking Pivot in TSQL if possible, but can I pivot with subheadings? How would i bind to it? Any suggestions on how to do it in C# if i have to (i can output to a html table if i had to)? I could export to excel by creating an openxml document using Microsoft's API.
Thanks, Rob