Looping through table and creating Dynamic columns

[#1]

I have the following table ORDER

CUST_ID	ORD_YEAR	Amount
A	2008	10
A	2008	15
A	2009	15
A	2010	20
A	2012	5
A	2014	10
A	2014	20
B	2008	10
B	2008	5
B	2008	25
B	2009	30
B	2013	5
B	2013	10
B	2014	5

Would like to create a summary table as shown below ORD_SUMM

CUST_ID	2008	2009	2010	2012	2013	2014
A	25	15	20	5		30
B	40	30			15	5

Tried the for loop but its throwing error.

Let vYRVal = min(ORD_YEAR);

for i=min(ORD_YEAR) to year(Today(0))

ORD_SUMM:

load

    CUST_ID,

    SUM(Amount) as $(vYRVal)

Resident

    ORDER

where

    ORD_YEAR = $(vYRVal)

group by

    CUST_ID;

vYRVal = $(vYRVal)+1;

next

drop table ORDER;

Any Idea on how to do this?

[#2]

Thanks Gysbert!

Still the back end data has multiple records

[Anonymous]

Makes sense.

(BTW, you don't need to group by $(i) anymore, although no harm either.)

The only remaining question is what to do with the dynamic front end.

[#4]

That helps even better for 2011 there were no data and now I have blank column for that .

[#5]

This is mostly  1 time deal. But I have similar reports where I dont use year as column name rather the difference between the current year and order year prefixed with YR as column name and the title for YR_0 ( which is 2014)  will be year(Today()) and for YR_1 (i.e 2013) will be (year(Today())-1) and so forth. The table is limited to past 5 years.

[Gysbert_Wassenaar]

Are you sure you opened the correct version? Perhaps saving it under a different name helps.

[#7]

Sorry, I was looking at the old one..