SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 flatten contacts records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

carriehoff
Starting Member

29 Posts

Posted - 11/12/2010 :  14:56:34  Show Profile  Reply with Quote
Hi all,

For the following table:

CompanyContactID CompanyID ContactFirstName
1 1000 Billie
2 1000 William

I'd like to write a query with a combined result, but in only one row:

a.CompanyContactID, a.CompanyID, a.ContactFirstName, b.CompanyContactID, b.CompanyID, b.ContactFirstName

So the results for the above table would be:
1 1000 Billie 2 1000 William

I tried to join the table to itself, which combined the number of rows from 2 to 4. I'd like to reduce the number of rows to 1.

Can anybody help?

Thanks,
Carrie

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 11/12/2010 :  15:16:31  Show Profile  Visit webfred's Homepage  Reply with Quote
Do it in front end and not in sql.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

carriehoff
Starting Member

29 Posts

Posted - 11/12/2010 :  15:18:07  Show Profile  Reply with Quote
Ok, that's certainly an idea, but I was really hoping this was possible within SQL.

Anyone else?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/12/2010 :  15:21:20  Show Profile  Reply with Quote
in you're example you joining

CompanyContactID = CompanyContactID + 1

Is that true???

How will you know the reulst will only b 4 rows?

Got anymore sample data?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

carriehoff
Starting Member

29 Posts

Posted - 11/12/2010 :  15:29:09  Show Profile  Reply with Quote
Ah, no I wasn't. I wasn't sure this was possible.
I was only joining on the one similar field I could see, company ID:

select c1.companycontactid, c1.companyid, c1.contactfirstname,
c2.companycontactid, c2.companyid, c2.contactfirstname
from companycontact c1
join companycontact c2
on c1.companyid = c2.companyid

However, I used your suggestion (not knowing that it was possible before today), and achieved the result I was seeking:

select c1.companycontactid, c1.companyid, c1.contactfirstname,
c2.companycontactid, c2.companyid, c2.contactfirstname
from companycontact c1
join companycontact c2
on c1.companycontactid = c2.companycontactid + 1

Thank you very much, this was exactly what I was looking for!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/12/2010 :  15:34:00  Show Profile  Reply with Quote
quote:
Originally posted by carriehoff

Ah, no I wasn't. I wasn't sure this was possible.
I was only joining on the one similar field I could see, company ID:

select c1.companycontactid, c1.companyid, c1.contactfirstname,
c2.companycontactid, c2.companyid, c2.contactfirstname
from companycontact c1
join companycontact c2
on c1.companyid = c2.companyid

However, I used your suggestion (not knowing that it was possible before today), and achieved the result I was seeking:

select c1.companycontactid, c1.companyid, c1.contactfirstname,
c2.companycontactid, c2.companyid, c2.contactfirstname
from companycontact c1
join companycontact c2
on c1.companycontactid = c2.companycontactid + 1

Thank you very much, this was exactly what I was looking for!



Great..now I'm doomed to SQL Hell for this ;-)

And Comapny = CompanyID as well

If you have more than 2 of these thing you will get a Cartesian product




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/12/2010 :  15:35:55  Show Profile  Reply with Quote
and you do that 2 is going to equal 3..

If you post some sample data, that would be good

Or do you want everyone listed out for a company?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/12/2010 :  15:43:27  Show Profile  Reply with Quote
Maybe you'd like to write a Function



CREATE FUNCTION [dbo].[udf_Category_csv]()
RETURNS @table TABLE 
(CompanyCd varchar(20), CertCategory varchar(8000))
AS 

BEGIN	
	 DECLARE  @CertCategory varchar(8000)
			, @CompanyCD varchar(20)
	 
	 DECLARE Cert_c CURSOR FOR
		SELECT DISTINCT CompanyCD FROM VENDOR_CATEGORY ORDER BY CompanyCD
		
	 OPEN Cert_c
     FETCH NEXT FROM Cert_c
	 INTO @CompanyCD

		WHILE @@FETCH_STATUS = 0
		  BEGIN
			  SET @CertCategory = null
			  SELECT @CertCategory = COALESCE(@CertCategory  + ', ', '')
					 + RTRIM(CertCategory)
			    FROM VENDOR_CATEGORY
			   WHERE CompanyCd = @CompanyCd 
			    
				INSERT INTO @table(CompanyCd, CertCategory) SELECT @CompanyCd, @CertCategory
				
				FETCH NEXT FROM Cert_c
				INTO @CompanyCD
		  END
		  
	  CLOSE Cert_C
	  DEALLOCATE Cert_C	
	  
	RETURN
END
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

carriehoff
Starting Member

29 Posts

Posted - 11/12/2010 :  16:03:42  Show Profile  Reply with Quote
Yes, thank you - I did catch that about matching up the company IDs. In my data's case, there are 2 types of IDs, and I simply wanted those results returned in one row. Your help made that very easy for me. THanks again.

Carrie
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000