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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Creating a table of information based from other tables

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
Go to Top of Page
   

- Advertisement -