I have an abstract relational database underneath some business objects... For instance, say I have two tables...

TABLE 1: A simple list of people...

ID USER
---------
1 Mike
2 John


TABLE 2: Name/Value pairs of attributes linked to table 1 by ID...

ID NAME VALUE
-------------------
1 Hair Brown
1 Eyes Blue
2 Weight 200

So you can see from this that Mike has brown hair and blue eyes, and that John weighs 200 lbs.

I want a query that selects a person and all their attributes (whatever they may be), and returns the results on one row like this (when run with a WHERE clause that selects user Mike).

USER HAIR EYES
-------------------
Mike Brown Blue


And returns this when run with a WHERE clause that selects user John...

USER WEIGHT
---------------
John 200

Any ideas? Thanks in advance!

Wow! Talk about stuff that will give you nightmares!

-PatP

This question has been asked and anwered hundreds of times in all forums. Do some research!
Hint: Search for PIVOT TABLE, CROSSTAB QUERY, etc...

I think this one is a twist on the usual cross tab... It looks like they want the query schema to change from row to row, which goes way beyond a cross-tab in my mind. It makes my head hurt just thinking about it. A cross-tab seems simple to code and use in comparison to this request.

-PatP

Thanks, Pat. I'm being bludgeoned on a few boards over this. People keep saying cross-tab, which is not what I'm after here...

It does indeed make the head hurt...

I would classify this as a candidate for a recursive query. Search through this forum for some good ideas.

candidate for dynamic sql

step 1: select distinct name from table2 where id=n

if you don't know n, do a join and use WHERE table1.name = 'fred'

step 2: construct multiple LEFT OUTER JOIN query, from table1 to table2 as many times as there are attributes that fred has (from step1), aliasing each table2.value to the corresponding column name

step 3: execute the dynamic query

easy peasy

I think that Rudy's suggestion is a good one, if you can allow all of the rows in a given result set to have the same schema. This is probably the closest answer possible to what you want using standard SQL tools. Supporting irregularly shaped result sets is possible using some tools, but not using standard recordset-oriented tools.

As Fibber used to say: "T'ain't pretty, McGee!"

-PatP