SQL Select and FoxCharts-What I missed ?

Jul 31, 2009 at 8:15 AM

The problem:
Supose I have a table to colect values by Year/membersID’s/Months like this:

MemberN

Year

Jan

Fev

Etc until Dez

1

2007

42642,85

53996,22

Etc until Dez

1

2008

34324,32

434534,55

Etc until Dez

1

2009

60956,44

23242,55

Etc until Dez

2

2007

5168,36

36254,85

Etc until Dez

2

2008

123131

455445,33

Etc until Dez

3

2008

62567,16

76282,36

Etc until Dez

3

2009

234223,34

2232,44

Etc until Dez

If the user wants a chart with totals year/month with all the months displayed for each year I issue the falowing select statement:

(In the program I the user is free to choose the range of years to show.)

SELECT SUM(Jan) as Janeiro,SUM(Fev) as Fevereiro,SUM(v_q_3)…,year FROM quotas INTO CURSOR quotasmes WHERE year >= 2006 AND year <= 2009 GROUP BY year

This result in that cursor (don't look at values)

Year

Jan

Fev

Etc until Dez

2007

42642,85

53996,22

Etc until Dez

2008

5168,36

36254,85

Etc until Dez

2009

62567,16

76282,36

Etc until Dez

With this cursor I cannot display a bar chart with years and months, even one year at a time. I must convert this cursor structure transforming the columns in rows and rows in columns Ex:

Row 1 : Year,Jan
Row 2 : Year (same), Fev
Row32 : Year (same), Mar

And so on
This will result in a huge table with many rows depending the number of years the user chooses.  

This is not doable programmatically recurring to SQL and most important is contrary whit the way tables are created.

My question is; How can I (thru a select statement) create a cursor with the right structure to obtain a bar char with months displayed.
This is because the way Foxcharts take values to display, by row. Each bar in the charts is taken by row not by column (the natural way the tables are created). In this example I cannot create a table to record the contributions from members where columns representing Years and rows representing members and months, this doesn’t make any sense.

Because of this the work for the programmer is very difficult as he must take values with SQL Select statements in a program due to the choices made by users instead of create cursors with fixed values, as the examples do.
Way foxcharts don’t take values the way tables are created?

To better understand what I mean, try to convert the static/Hardcoded cursors in the examples with cursors created with SQL Select statements from Northwind Database.

Thanks