sql server programming:
Hi there

I am using sql server and am very stuck on combining results from
multiple tables in one row.

I have multiple tables containing different information on dogs and I
want one row per breed holding all of the information I need. I have
created a view:
dogbreedID, dog size, dog colour etc
by combining lots of different tables using joins

I also have the values:
dogBreedID,criteriaID,value, location
1, 2, red,filpath1
1, 3, pink, filpath2

etc in another table

and I want to have one table that has

dogbreedID, size, colour, criteria1value, criteria1filepath,
criteria2value, criteria2filepath

so there is just one row containing all of the information.

can anyone help?

I am also happy to pay someone to help as my mind is blown :).
Obviously the data is much more complex than this.

Vivienne

vivienne flower (chattin2@hotmail.com) writes:
[quoted text, click to view]

It seems that your major problem is the table with dogBreedID and
criteriaID. You could turn the rows into columns this value:

SELECT dogBreedID,
crit1value = MIN(CASE criteriaID WHEN 1 THEN value END),
crit1location = MIN(CASE criteriaID WHEN 1 THEN location END),
crit2value = MIN(CASE criteriaID WHEN 2 THEN value END),
crit2location = MIN(CASE criteriaID WHEN 2 THEN location END),
...
FROM tbl
GROUP BY dogBreedID

then you join that table with the other table.

Obviously, you need to know how many criteriaIDs there might be.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]
Is there a set number of criteria values and filepaths or can it get
arbitrarily large?

--
Message posted via http://www.sqlmonster.com

The design was intended so that it could grow arbitrarily however that
makes it so much more complicated I didnt realise I would get so stuck
on this stuff :)

I could fix it to be static. Currently I have 23 criteria.

Vivienne

All working

thanks so much!!

Vivienne

[quoted text, click to view]

And we would be happy to take your money :-) But in return you get
value and piece of mind. And what are those worth?
Check out RAC, it doesn't care about the 'how many' question.
Any crosstabs, concatenation over rows it will do things dynamically
and most importantly easily. Do you really care that much how it works? :-)

RAC (Relational Application Companion) @
www.rac4sql.net

And for those that really care about how things do work:
www.beyondsql.blogspot.com

..

[quoted text, click to view]

Just my luck :( :)

[quoted text, click to view]

Instead of this mess, have you considered normalizing your database?
That would mean that all the attributes of an entity are in one table
to start with.