How can I get a value for a record to show as a field in a view 1

[nysouth]

The data I am working with is like this:

ID
Name
Year (string value entered)
Amount

I want to have a view that would take that information and give the following:

ID
Name
Amount (if the year value is X) AS X Amount
Amount (if the year value is Y) AS Y Amount

Right now we only have two years worth of data. This record is done on an annual basis to show year end amounts. I anticipate having to add a new column to this view each year as years go on...or is there even a way to elimnate this step?

Advice is greatly appreciated.

 

[tektipdjango]

select
ID,
Name,
sum(yearX) as Xyear,
sum(yearY) as Yyear

from

(select
ID,
Name,
(case Year
when X then Amount
else 0
end) as yearX,
(case Year
when Y then Amount
else 0
end) as yearY
from Data)
as DataP

group by Id,Name

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[nysouth]

Thanks for the tip on constructing the query, it worked great. But for some reason Crystal will not use this query. It says there is a syntax error on line one near the column title. I also notice it adds a period in front of the column name (I called it '2001-2002' and it is reporting it back like '.2001'.

The second error message comes back with error detected by database dll.

Any ideas?

 

[nysouth]

Nevermind, it looks like it was the field name I gave it.

Thanks!
Colleen