TheunisP
Posts: 342
|
| Posted: 12/21/2005, 2:55 PM |
|
Hi 1 and all, need some help on this:
- I have a table with article content
- each article is classified as being in a specific category
- categories is another table so the number can vary (read: hard coding as a solution wont work)
I want to build a directory like display with say the last 5 articles of each category display in something like this:
cat 1 cat 2 cat 3
- a1 - a2 - a3
- a4 - a5.......
cat4 cat 5 .....
- a20 - a21 etc
I know this is a tall order, but some help would be appreciated - for the rest you all have a merry Xmas & a very prosperous new year 
Theunis
|
 |
 |
Paul Shearing
|
| Posted: 12/22/2005, 4:59 AM |
|
Hi Theunis
What you have described is a form of cross-tabulation (data pivoting) where, essentially, rows and columns are swapped. if you are using Microsoft Access as your data engine then this is fairly straigtforward because you can generate a crosstab query to do the work for you. However, there are two problems that I can see:
1) If you are using a different type of database ie MS SQL 2000 then there is no crosstab capability built-in.
2) There is potentially a varying number of columns in the grid because, presumably, your categories are being updated from time to time. I don't think that CCS can cope with a varying number of columns in its grids.
If your categories are static then you could generate your display by having a grid where each column was populated via a separate query that used a SELECT TOP 5 ArticleTitle FROM Articles WHERE (Category = CatNo) type of appraoch, but this would require heavy customisation of the CCS code for grids.
To do this efficiently in CCS would require that you write some code in your application to populate a temporary table with the necessary fields for display. A table with as many columns as there are categories and five rows of data would do the trick - this is the table you would display using CCS and this is essentially hand-coding the crosstab that I mentioned above.
You might be able to achive the desired table using Excel. Export the data to a CSV file, then using COM automation, open the file in Excel and perform a data pivot. Then write the pivoted data to a CSV file and import this to your temporary table.
If you are using SQL 2000, have a look at: http://searchsqlserver.techtarget.com/tip/1,289483,sid8...4&ad=525294USCA
This describes a freely-available SQL 2000 stored procedure that claims to perform crosstabs. I have not used it in anger, but it does appear to work with the examples given and seems surprisingly fast.
Perhaps there is a much simpler way of achieving what you require - so, over to other, cleverer people in this forum
|
|
|
|
TheunisP
Posts: 342
|
| Posted: 12/22/2005, 5:15 AM |
|
P, thanks for the reply, I anded making a column grid for the categories - in the display of the category - I to a select top 5 of the articles of that category (same as you suggested) - I was wishing for an easier solution (I was lazy and tired).
The current solution seems fine and fast enough - thanks again for the suggestions - will also have a look at them.
T
|
|
|
|
|
