Multiple Columns in One Record to One Column

[aggieval]

I have a budget table with one record for each account and a field for each month. I would like to set up a query, for a report, that would put the data in a format so that each month's budget is in one column, a record for each month.

Any suggestions on setting this up would be incredibly helpful.

Tracy

 

[gny]

I guess there are prettier ways to do this but try this:

SELECT 'jan' AS month, SUM(Jan)
FROM budget
UNION
SELECT 'feb' AS month, SUM(Feb)
FROM budget
UNION
SELECT 'march' AS month, SUM(Mar)
FROM budget

and so on....

/gny

 

[aggieval]

Thank you so much, even if it's not pretty, it should work.

Tracy

 

[Griffyn]

I was about to post a similar question, but your solution isn't suitable for me.

The table I'm working on is linked via ODBC and takes at least 5 seconds to get the data - and I've got 15 fields I want to merge in this way, so if I can avoid querying the table 15 times it would be nice.

I've got an Inventory table that I will have filtered out to just one stock code, but we have multiple warehouses (one per record), and 15 price levels per warehouse (PRICE1, PRICE2, PRICE3, etc.)

I want to end up with a table with the warehouses as the columns, and 15 rows to show each of the price levels in each warehouse.

Can anyone help? I could use the union suggestion to get 15 records per warehouse, and then use a crosstab query on it, but I'd like something more efficient.