SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Creating a table of information based from other tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/26/2001 :  11:08:07  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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."

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/26/2001 :  11:37:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT P.Fname, P.Lname, P.Sex,
COUNT(CASE F.Pref WHEN 'apples' THEN F.Pref ELSE Null END) AS Apples,
COUNT(CASE F.Pref WHEN 'oranges' THEN F.Pref ELSE Null END) AS Oranges,
COUNT(CASE F.Pref WHEN 'pears' THEN F.Pref ELSE Null END) AS Pears,
COUNT(CASE V.Pref WHEN 'carrots' THEN F.Pref ELSE Null END) AS Carrots,
COUNT(CASE V.Pref WHEN 'potatoes' THEN F.Pref ELSE Null END) AS Potatoes
FROM Person AS P LEFT JOIN VegPref AS VP ON (P.PersonID=VP.PersonID)
LEFT JOIN FruitPref AS FP ON (P.PersonID=FP.PersonID)
INNER JOIN Vegetable AS V ON (V.VegID=VP.VegID)
INNER JOIN Fruit AS F ON (F.FruitID=FP.FruitID)
GROUP BY P.Fname, P.Lname, P.Sex
WITH ROLLUP


I would recommend you combine the Vegetable and Fruit tables, which would allow you to combine the Pref tables for each as well. Just add a Type column to distinguish between fruits and veggies (F or V, for example). You'll GREATLY simplify your queries; 3-way joins are better than 5-way joins.

I also think you should ditch the VegID/FruitID columns completely, and use "carrots" and "apples" directly. It would eliminate one more table from the query. You should keep the fruits/vegs table for FOREIGN KEY constraints, but you won't need it in a query just to determine VegID=1 equals "Carrots".

Thanks for this question, I was looking for a good example to reference in an article I'm writing on primary keys and identity. My best advice is don't use identity if you don't need it, and your tables don't need it.

Edited by - robvolk on 02/26/2001 11:38:12
Go to Top of Page

tintin31
Starting Member

USA
14 Posts

Posted - 02/26/2001 :  16:45:07  Show Profile  Reply with Quote
Thank you for your response.

The scenario I have presented here is a simpler one than the actual problem I am tackling. In terms of the Fruit and Veg table I will have more than 2 fields in the table (i.e. any where from 10-15 fields). Therefore I would prefer to have an id column. Plus I can see that you are hardcoding the values of these tables in the query. In my real situation I am not going to know what the values are and they may change over time. So harding coding will be problematic for me. I need away to get the values that will be in my "Fruit" or "Veg" tables and present them as column headings with out knowing or caring what those values are. Plus I will have more tables than just "Fruit" and "Veg". I have a total of seven tables that will have records which I would wish to present as columns.

I am thinking that I can not achieve this as a straight forward select. I believe that I would have to write a stored procedure to process the information.

I would appreciate your response to this.



Edited by - tintin31 on 02/26/2001 16:56:57
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/26/2001 :  17:11:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
Can you post all your table structures? I'd like to see the whole thing, because I still think you don't need an ID column. If fruits/vegs will be unique anyway, you're better off using that as primary key. Depending on your current structure, and if you can change it around, you could possibly write this as a straightforward query.

As far as dynamic column values, it is problematic to do this (no TRANSFORM statement like Access has!), but there are several articles here on Dynamic SQL. You could create a SQL statement on the fly that includes all of the columns, without knowing them in advance. Having an ID column won't solve that problem, if you think about it.



Go to Top of Page

tintin31
Starting Member

USA
14 Posts

Posted - 02/26/2001 :  19:33:47  Show Profile  Reply with Quote
I know I'm getting tired because I have a real headache and my previous response had a few technical untruths. So let me correct.

My fruit and veg tables are in my real life situation just lookup values of which they tend to be just 2 fields with an id column and a value. So if you say I can dispense with an id then I can undertstand that point. I have at least 8 lookup tables that would need to be referenced to get the value inorder to create my column headings.

My FruitPref and VegPref tables are the ones that may have any where between 10-15 fields pertinent to that table. They will contain the various records that relate to various people's selections. These table total approx 10.

So using the Pref tables and cross referencing the lookups in order to get the values which will be my column headings I want to be able to create a recordset displayable as follows (this will not render as a table unfortunately but if you cut and paste you will see what i would wish to see):

<TABLE>
<TR>
<TD>fname</TD>
<TD>lname</TD>
<TD>sex</TD>
<TD>apples</TD>
<TD>oranges</TD>
<TD>pears</TD>
<TD>carrots</TD>
<TD>potatoes</TD>

</TR>
<TR>
<TD>David</TD>
<TD>Jones</TD>
<TD>M</TD>
<TD>x</TD>
<TD> </TD>
<TD>x</TD>
<TD>x</TD>
<TD> </TD>
</TR>
<TR>
<TD>Kate</TD>
<TD>Holmes</TD>
<TD>M</TD>
<TD> </TD>
<TD>x</TD>
<TD>x</TD>
<TD>x</TD>
<TD>x</TD>
</TR>
<TR>
<TD>Mike</TD>
<TD>Silk</TD>
<TD>M</TD>
<TD>x</TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD>x</TD>
</TR>
<TR>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD>2</TD>
<TD>1</TD>
<TD>2</TD>
<TD>2</TD>
<TD>2</TD>
</TR>
</TABLE>

You are correct in suggesting the TRANSFORM function which would have gone someway in giving me what I wanted but alas, SQLServer is lacking in this area.

So essentially I need to replicate the TRANSFORM capbilities in order to produce what is ultimately a crosstab or pivot table.

So yes, I will have to look into dynamic SQL. In order to do this TRANSFORM I am thinking that I can:
1. go through the Pref tables to find the unique records that reference FruitID, VegID etc (or the values)
2. reference the IDs in Fruit/Veg tables to obtain the values to act as columns.
3. store this list in a temp table which will be a list of my headings
4. create another temp table which will have responses for a person relating to each of those headings (use of a "x")
5. given the number of records in the temp heading tables, will tell me how many columns are to be created in my 3rd temp table utilising dynamic sql. This table will essentially be my grid of the combined information of the previous 2 temp tables.
6. the first row in this temp grid table will be the records in the temp heading table. Records after that will be records from the temp responses table

I could send you the table structures but would prefer to send that privately.




Edited by - tintin31 on 02/26/2001 20:04:31
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/26/2001 :  20:14:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes, could you send the structures to my e-mail address? mailto:robvolk@hotmail.com. I'll need to see the extra columns before I can tell whether it can be done or not. Thanks!

Go to Top of Page

paulmelba
Starting Member

8 Posts

Posted - 05/15/2007 :  15:47:38  Show Profile  Visit paulmelba's Homepage  Click to see paulmelba's MSN Messenger address  Reply with Quote
Personal Loan SPAM deleted

Edited by - jsmith8858 on 05/15/2007 16:03:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000