Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2001-02-26 : 11:08:07
|
Sue writes "This is a logic programming problem which may be a bit tricky for me to explain but I will do my best.
Give the following 5 tables of records:
Person Table PersonID Fname Lname Sex 1 David Jones M 2 Kate Holmes F 3 Mike Silk M
Vegetable Table VegID Veg 1 Carrots 2 Potatoes
Fruit Table PrefID Pref 1 Apples 2 Oranges 3 Pears 4 Bananas
FruitPref Table PersonID FruitID 1 1 1 3 2 2 2 3 3 1
VegPref Table PersonID VegID 1 1 2 1 2 2 3 2
I wish to return a recordset as follows:
fname lname sex apples oranges pears carrots potatoes David Jones M x x x Kate Holmes F x x x x Mike Silk M x x …. Total 2 1 2 2 2
Using the PersonID, I wish to search the FruitPref tables to see if a person has any records. Similiarly, I check to see if the person as records in the VegPref table.
I would then present a single row, identifying what a person has choosen identified by an "x" and also have a running total for each column at the end.
I think that what needs to be established first are the distinct values in the FruitPref and VegPref table. To do this will give the columns that are required. Then each record in the Person table needs to be parsed and check against the FruitPref and VegPref table to identify for that individual which fruits and vegetables he/she has choosen.
It will be unknown how many columns there will be in terms of the FruitPref or VegPref because new records can be added. And if it is a distinct value in the table it because a column. It will be known which columns (and therefore how many columns) will be displayed from the Person table
So really my question is, what is the best why to tackle this problem. I am at a lost to know the best why to assemble the information in order to get the single row record that I will need and to know how to create the appropriate structure to store this information. I believe I must use temporary tables to act as arrays for intermediate processing and for the final data.
I’m uncertain given that I will not know how many columns I will need for this table, whether I can create a table via dynamic sql with generic column names (i.e. col1, col2, col3 etc). The number of columns that are created would be based upon the appropriate number of distinct columns from my required tables (i.e. FruitPref and VegPref)
Any suggestions are most welcomed." |
|
paulmelba
Starting Member
8 Posts |
Posted - 2007-05-15 : 15:47:38
|
Personal Loan SPAM deleted |
|
|
|
|
|