Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Multiple rows into one query result row...

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow! Talk about stuff that will give you nightmares!

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    This question has been asked and anwered hundreds of times in all forums. Do some research!
    Hint: Search for PIVOT TABLE, CROSSTAB QUERY, etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LKBrwn_DBA
    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

  5. #5
    Join Date
    Feb 2003
    Posts
    5

    Yep...

    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...

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    I would classify this as a candidate for a recursive query. Search through this forum for some good ideas.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •