Is it possible to make a MS-SQL Scalar function do this?
- by Hokken
I have a 3rd party application that can call a MS-SQL scalar function to return some summary information from a table. I was able to return the summary values with a table-valued function, but the application won't utilize table-valued functions, so I'm kind of stuck. Here's a sample from the table:
trackingNumber, projTaskAward, expenditureType, amount
1122, 12345-67-89, Supplies, 100
1122, 12345-67-89, Supplies, 150
1122, 12345-67-89, Supplies, 250
1122, 12345-67-89, Misc, 50
1122, 12345-67-89, Misc, 100
1122, 98765-43-21, General, 200
1122, 98765-43-21, Conference, 500
1122, 98765-43-21, Misc, 300
1122, 98765-43-21, Misc, 100
1122, 98765-43-21, Misc, 100
I want to summarize the amounts by projTaskAward & expenditureType, based on the trackingNumber. Here is the output I'm looking for:
Proj/Task/Award: 12345-67-89
Expenditure Type: Supplies
Total: 500
Proj/Task/Award: 12345-67-89
Expenditure Type: Misc
Total: 150
Proj/Task/Award: 98765-43-21
Expenditure Type: General
Total: 200
Proj/Task/Award: 98765-43-21
Expenditure Type: Conference
Total: 500
Proj/Task/Award: 98765-43-21
Expenditure Type: Misc
Total: 500
I'd appreciate any help anyone can give in steering me in the right direction.
