SQL Query with Dynamic Columns Question

[cojiro]

I have three tables in a relational database: User, Expense, and ExpenseDescription.

User Table Fields: UserID, Name, Age

Expense Table Fields: ExpenseID, Amount, ExpenseDescriptionID, UserID

ExpenseDescription Fields: ExpenseDescriptionID, Description

Problem: I need to write a summary query that would return the following Columns:
User.Name, [sum of Expense.Amount for each type of expense] as ExpenseDescription.Description

This is where [sum of Amount for each type of expense] would create a column for every entry in the ExpenseDescription table. I have no idea where to begin on this. Does anyone know how I should go about this? Please let me know if I need to clarify this problem.

Here is a sample of the desired output if ExpenseDescription only had 3 columns:

Name RentalCar Hotel Misc
Joe 0 200 10
Bob 100 130 0

 

[PWise]

look @ pivoit tables

 

[cojiro]

Yeah, I know practically I could just run this normally, throw this in a pivot table and get my disired results. The problem is I would like to take the results of this query and use it as a datasource for a gridview control on a webform. So my options here are kind of limited...

 

[PHV]

Can't you use a CrossTab query as the datasource of your gridview ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cojiro]

I'm not sure, I'll give it a try though...