would you found a query?? 1

[pondi]

Hello all!
I got a database like this:

Table Element: Table DataValues: Table Datatype:
ID NomElt ID Owner Type Val TypeID TypeNam
1 Elt1 1 1 0 32 0 length
2 Elt2 2 1 1 65 1 weight
3 Elt3 3 3 1 21 2 height

All right, as you can see i have a list of elements, each one owns a few data values and each value has a specific type.
I'm making an ASP page and like to make an array with elements in one column and all data types on row:

length weight height
Elt 1 32 65 xx
Elt 2 xx 21 xx
Elt 3 xx xx xx

Ok so i'd like to find such a query on my database, i work with SQLServer... I made a nice message huh? so please help me, even tell me if it's impossible because my database design is bad. THHAAAAANKS!

 

[tlbroadbent]

The best answer depends on your database. In MS Access you would create a crosstab query. Here is a suggestion for Microsoft SQL Server.

Select
NomElt,
Case Type When 1 Then Val Else 'xx' End As Length
Case Type When 2 Then Val Else 'xx' End As Weight
Case Type When 3 Then Val Else 'xx' End As Height
From DataValues a Join Element b
On a.Owner=b.ID Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[pondi]

Thanks for your help, i think that's a good progress,i just had to force '00' instead of 'xx' for type trouble. But the result of my query is now:

length height weight
Elt1 21 0 0
Elt1 0 45 0
Elt2 0 18 0
Elt2 0 0 98

I'd like to have only one row per element...

 

[tlbroadbent]

I knew you only wanted one row per element. I just failed to add the necessary SQL. This is it.

Select
NomElt,
Max(Case Type When 1 Then Val Else 0 End) As Length
Max(Case Type When 2 Then Val Else 0 End) As Weight
Max(Case Type When 3 Then Val Else 0 End) As Height
From DataValues a Join Element b
On a.Owner=b.ID
Group By NomElt Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[pondi]

EX-CEL-LENT
Thanks for all!!