Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    46

    Unanswered: Complex Query Question

    I have a question where for the most part I am linking tables that have many one to one relattionships in them. Let's say, now that I have a table that has a one to many relationship and I want to add it to my SQL Statement.

    The problem that is arising is that I am now getting duplicates in my query.
    Is there any way that I can take the many rows in the table with the 'MANY' in it and somehow combine the field that I would like into one row.

    The example below may illustrate what I am trying to do.




    CASEID CASEID LAST_NAME FIRST_NAME
    2006_R1001 2006_R1001 JONES JIM
    2006_R1001 STEVENS TOM



    So, instead of having two rows in my resultset like
    LAST_NAME, FIRST_NAME
    2006_R1001 JONES JIM ROW1
    2006_R1001 STEVENS TOM ROW2


    I would like
    COMBINED_FIELD
    2006_R1001 JONES JIM + STEVENS TOM ROW1


    Could someone provide some examples. I also need to use the SQL 99 syntax in that my query needs to use this for its flexibility.

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

    Thumbs down


    Complex?

    This question has been asked and answered hundreds if not thousands of times -- do some research!

    Hint: Try searching the term 'pivot' or 'crosstab' or 'rows to columns' for complexity.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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