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!

---

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