Expected Formatted Results?

Hi All,
I am kindly seeking for help.
I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
Date ID R P M D... Y
1/1/90 A 1 2 3 4... 5
1/2/90 A 2 3 4 5... 1
...
2/11/05 A 3 4 5 6... 2
1/1/90 B 1 2 3 4... 5
1/2/90 B 2 3 4 5... 1
...
2/11/05 B 3 4 5 6... 2
...
The expected query results look like: ( this results from Date, ID and R fields)
Date A B
1/1/90 1 1
1/2/90 2 2
...
2/11/05 3 3

The SQL I wrote:
select date, ID,
A=sum(case when ID=A then R else 0 end),
B=sum(case when id=B then R else 0 end)
from MyTable
Group by date

I would also like to get another set of results with the same format but from date,ID and P fields:
Date A B
1/1/90 2 2
1/2/90 3 3
...
2/11/05 4 4

select date, ID,
A=sum(case when ID=A then P else 0 end),
B=sum(case when id=B then P else 0 end)
from MyTable
Group by date

The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.

Any suggestion/comments are highly appreciated!
shiparsonsFor the moment you have to do it this way, unless you want to get into hairy dynamic SQL.

And anyway, if you do not hard-code the column names in your crosstabs then good luck creating any reports based upon them (Crystal, Access, or anything else). Reporting applications tend to require advance knowledge of the data layout they use...|||Not because I do not trust your comments...I am not sure if I know SQL well enough to make a judgement. Is there another way to go around? Could you please elaberate "hairy Dynamic SQL"?

Many thanks,
shiparsons|||I agree with blindman, this problem is practically impossible to solve on the server. However, there are many, relatively simple solutions to this problem on the client side.

Depending on what tool you choose to use, the wording and methods are slightly different. Excel has pivot tables (http://www.microsoft.com/technet/prodtechnol/office/office2000/support/helpofc.mspx), MS-Access has crosstab reports (http://support.microsoft.com/default.aspx?scid=kb;en-us;328320), etc. Most client analysis tools support this type of activity in some way.

-PatP|||Hairy dynamic SQL, meaning you will have to access the system tables or SCHEMA tables to locate the columns for your table, and then loop through each one of them constructing your SQL statement as a string before executing it.