Thread: Complex Query Question
12-05-06, 11:53 #1Registered User
- Join Date
- Feb 2006
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
2006_R1001 JONES JIM ROW1
2006_R1001 STEVENS TOM ROW2
I would like
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.
12-05-06, 12:01 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
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