Russ,
All right. Let me take a shot. First off, I don't think I really
understand the ins and outs of your data, but the formatting issues
remind me of a problem that I had outputting data, so I'll punt:
Generally stated, you have data that is grouped by some geographic area.
Within each geographic area group, you have one or more values, each
applying to a particular factor. A given factor may have data within
one geographic area group, but not within another. If all that is
right, then read on.
You pull the data and group it. You then either run a separate query or
loop over the initial query to pull all unique values for factor that
appear at least once in the query, regardless of the number of
appearances. You order that list however you like (alphabetical by
factor name, for example).
Now comes the output. First off, the table structure is an outer table
with one row and multiple columns. Each of the cells is an inner table
with one column and as many rows as in your query listing unique factor
values plus one (gots to have a header!).
In your first column, loop over the list of unique factor values and
output them.
In each of the other columns, again loop over the unique factor values,
but now you are looking within the real data query for the data you
want. There are probably a bunch of ways to do this. (I'm sure
whatever you come up with will be at least as good as my clumsy method.)
In any event, the idea is that if you find a corresponding value, you
put it in, and you otherwise leave 0 or "-" or whatever.
HTH,
Matthieu
-----Original Message-----
From: Snake [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 07, 2006 4:55 PM
To: CF-Talk
Subject: RE: FW: Outputiing dynamic columns
Having had a quick read, pivot tables don't appear to exist in SQL
Server 2000, which is what I am using.
Russ
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: 07 January 2006 18:35
To: CF-Talk
Subject: Re: FW: Outputiing dynamic columns
Snake wrote:
[http://www.satachi.com/russ/lists/yf.html]
> This is basically market research data that can be National, regional
> or local.
> If the locale is national, the data applies only to countries.
> If the locale is regional, the data applies to regions of countries.
> If the local is local, the data applies to a specific ity of a
> specific region of a specific country.
>
> Any factor may thus have any number of rows of data in the DB as it's
> data could be acros smany countries or regions, or just for a single
city.
> There will be one row for each column of data in the output, which is
> made up of a country (required), region(optional) and city (optional).
> The output needs to be groupe dby the factorID.
>
> As you can see form my example, the factor called "regional test" has
> 4 columns as it has been recorded for multiple regions in the UK.
> The "students" factor only has data for 1 region, and thus will only
> have 1 column to output.
>
> In the same way, other factors could more or less columns.
>
> I this respect I think I probbaly cannot have a single heading for
> everything and will need to have new headings for each factor,
> otherwise they will not line up.
You can have a single heading for all, but you run the risk you will
have many localities in the header so your table gets very wide, and
rows that only have data in one of them and are thus mainly empty. With
the 4 UK regions in your example it is not that bad, with 50 US states
where you have only one data point it is bad. That is a design issue you
need to decide first.
Another thing to consider is displaying the data hieragically.
Instead of the example you show, you could use something like:
United Kingdom
| Wales | Kent | Yorkshire | East England |
2002 | 2 |
2003 | 3 |
2004 | 3 | 5 | 7 | 4 |
2005 | 4 | 6 | 5 | 6 |
Anyhow, you need to read up on pivot tables. Google will lead you to
some scripts that will help you a lot by doing the hard work in SQL.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229522
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
