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:228736 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
