Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008

    Unanswered: Joins: Rows to columns?

    I'm wondering about the situation where you have a set of objects in one table and a variable set of object attributes in another table.

    Take the example of a person table and a person_skill table in a one-to-many relationship:

    PERSON{ id, name }
    PERSON_SKILLS{ person_id, skill_name}

    Is it possible to perform a query that will select all people, with their skills listed in the same row as the person? In other words:

    1 Al
    2 David
    3 Mary

    1 Archery
    1 Bowling
    1 Eating
    2 Shoe Tying
    3 Scuba Diving
    3 Wrestling

    An get a result set like:

    ROW1: Al, Archery, Bowling, Eating
    ROW2: David, Shoe Tying
    ROW3: Mary,Scuba Diving, Wrestling

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    in MySQL, it's trivial: use the GROUP_CONCAT function

    in SQL Server, yes, it's possible, but it's really ugly

    my advice: do it in the application layer | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Here are instructions for SQL Server 2000.
    For 2005, look up CTEs (Common Table Expressions) which are a better method of implementing this.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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