Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Multiple Table Combination

    I would like to create a query that would generate the output table, but I'm not sure where to start.

    [USER TABLE]

    userID fName lName
    1 Joe Smith
    2 Mike Brown

    [CATEGORY TABLE]

    categoryID title
    1 category1
    2 category2
    3 category3

    [USERCATEGORy TABLE]

    userID categoryID
    1 1
    1 3
    2 2
    2 3

    [OUTPUT TABLE]
    (comma seperate rows

    fName lName category1 category2 category3
    Joe,Smith,x, ,x
    Mike,Brown, ,x,x

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a classic cross-tab query. There is a generic solution, and there are database engine specific solutions. The generic solution is a lot more work, and it is vulnerable to some kinds of data errors.

    Can you give us a bit more background so we can help you out?

    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    The rdbms is ms sql 2000. I was debating wether to post it here or in that category. It will lead to an output as an excel file, that is why I want the x's in the category, but for now i'm just interesting the sql to generate this. If a sql solution is not advisable I welcome pointers in t-sql that might help me with this.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try doing a search for cross-tab in the SQL Server forum. I've answered this type of question at least a dozen times there, you should be able to find a decent hit or three!

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Books On Line has an excellent explanation and example of Crosstab queries. Look it up.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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