Trying to combine two records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to combine two records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-10 : 09:41:46

Tesfa writes "I am a Crystal Reports 11 user that is creating a report based on a view I created. What happens with the view is I will have the same case number (used to identify the customer) that has a contact information and contractor information. When I created the SQL view, SQL Server 2005 sees two of the same case number but on two record that have contract information and the other has the contractor information. When I run the report it multiples the pages, if there is a Case Number without contractor info it makes my two page report which I want. Visual example below of View Resultset

Case# ContactFirst ContactLast ContractorName ContractorType
1100 Tom William Null Null
1100 Null Null FF Labor Sub_Contractor

This is a simple example of what I am facing. Technically thats correct but Crystal doesnt like it.
Question: Is there a way where I can combine only the Contractor information to the record set that has the Contact information. I tried Concatenation but realize I need to still use the columns in Crystal Reports. Example of what I am looking for:

Case# ContactFirst ContactLast ContractorName ContractorType
1100 Tom William FF Labor Sub_Contractor

Thats how the application the clients use sees it and it works for Crystal Reports. Thanks. I am a Sams 21 days of SQL Server 2000 - 2005 and not bad with writing the code but I have fellow programmers that can help me understand your solution if your able to find one. Thanks again."

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-10 : 10:40:19

Follow the first link in my sig and try restating your question.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 01:31:26

[code]select [Case#], max(ContactFirst), max(ContactLast), max(ContractorName), max(ContractorType)
from table
group by [Case#][/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources