SQL - Select records side by side

Is there any relationship as to why (or who) goes side by side ?

is LHS  always buyer and RHS an engineer ?

Or could it also be
name1, name2
name3,name4

Mark, just to clarify..

>> Is there any relationship as to why (or who) goes side by side ?
is LHS  always buyer and RHS an engineer ?

Please see the columns ck_buyer and ck_engineer in the sample data which should answer your question..
FYI, I've handled in the query I've provided above as well..

Yep, know of those columns

My question was deeper than the obvious.... Otherwise it is just as legitimate to have Name1  Name4  Or Name6

So, it is reasonable to expect id_customer, but there are other columns like jobtitle that might help a matching process.

What I dont want to do is simply match the first buyer with first engineer when one is Job2 and the other is job3, or one is shippingadd 407 matching with shippingadd 515.

Contact_ID isnt really a meaningful order by - ID's which we dont know (could guess) what they represent - a job ? a call ? a person ? in CRM speak contact_id could be a lot of different things.

Hence the question, are there any relationships. I had a solution in mind when asking the question.... For you and me, getting rows side by side isnt too difficult (programmatically), but the real difficulty is making sure it meets the Askers need or expectation, given they have knowledge that we dont.

I see in your response, you also raise "not sure whether id_customer is related or not and please add/comment out this condition accordingly" So, no real difference except I asked before posting....

>> My question was deeper than the obvious....

Yes, may or may not be that deeper for the author till author clarifies more on the table or database structures(not sure whether author can share that info as well)

@Raja

not sure whether id_customer is related or not and please add/comment out this condition accordingly

Yes.  It is related.  It comes from the customer table.  I need to join the customer table with the CTE results.

Having issues to get it to display correctly when adding customer table.

; with cte as(
	select id_contact, fullname, id_customer, ck_buyer, ck_engineer
	from CUSTOMER_Contacts)
	, buyer as (
		select id_contact, fullname, id_customer, ROW_NUMBER() over (order by id_contact) rnum
		from cte 
		where ck_buyer = 1
	)
	, engineer as (
		select id_contact, fullname, id_customer, ROW_NUMBER() over (order by id_contact) rnum
		from cte 
		where ck_engineer = 1
	)

SELECT 
	 C.CustomerId as id_customer 
	--,C.Name as Name 
	,'Test' AS Name
	
	,CA1.companyName AS BillingCompanyName
	,CA2.companyName AS ShippingCompanyName

	,B.FullName
	,E.FullName

	,CONVERT(VARCHAR(50), PT.Name) AS Terms
	,CONVERT(VARCHAR(250), CTC.comment) AS TermsComment

--INTO #Temp
FROM BASE_Customer C 
LEFT JOIN Customer_Address CA1 ON CA1.id_customer = C.CustomerId AND CA1.Name = 'Billing'
LEFT JOIN Customer_Address CA2 ON CA2.id_customer = C.CustomerId AND CA2.Name = 'Shipping'
LEFT JOIN BASE_country CO1 ON CO1.rowid = CA1.id_country 
LEFT JOIN BASE_country CO2 ON CO2.rowid = CA2.id_country 
LEFT JOIN buyer B on B.id_customer = C.CustomerId
LEFT JOIN engineer E ON E.id_customer = C.CustomerId
LEFT JOIN BASE_Customer_Terms CT ON CT.customer = C.CustomerId
LEFT JOIN BASE_PaymentTerms PT ON PT.PaymentTermsId = CT.term
LEFT JOIN BASE_Customer_Terms_Comments CTC ON CTC.customer_term = CT.id

WHERE C.IsActive = 1  
AND C.Name Like 'XXXXX%'
AND [distributor] = 'False'
ORDER BY C.Name

Select allOpen in new window

@Mark, Issue I'm having is that the buyer will display twice and then an engineer for each buyer giving me a total of 4 records.  Looking at the data there are two buyers and two engineers, so joining the customer table I wanted just two records with each buyer and engineer.

Yep,  can see the problem, let me do some testing, and will be back soon....

Can you pls provide the query to derive the result set in the question provided above so that we can modify it accordingly..
Since we don't have access to your table structures that will work better for us..

Was not needed... Pity you couldnt have waited another 10 minutes.... I do have the id_contacts side by side....

Want to re-open ?


( I can reopen for you)

So, guessing that your *real* query that seems to be working is :

SELECT 
	 C.CustomerId as id_customer 
	--,C.Name as Name 
	,'Test' AS Name
	
	,CA1.companyName AS BillingCompanyName
	,CA2.companyName AS ShippingCompanyName

--	,B.FullName
--	,E.FullName

	,CONVERT(VARCHAR(50), PT.Name) AS Terms
	,CONVERT(VARCHAR(250), CTC.comment) AS TermsComment

--INTO #Temp
FROM BASE_Customer C 
LEFT JOIN Customer_Address CA1 ON CA1.id_customer = C.CustomerId AND CA1.Name = 'Billing'
LEFT JOIN Customer_Address CA2 ON CA2.id_customer = C.CustomerId AND CA2.Name = 'Shipping'
LEFT JOIN BASE_country CO1 ON CO1.rowid = CA1.id_country 
LEFT JOIN BASE_country CO2 ON CO2.rowid = CA2.id_country 
--LEFT JOIN buyer B on B.id_customer = C.CustomerId
--LEFT JOIN engineer E ON E.id_customer = C.CustomerId
LEFT JOIN BASE_Customer_Terms CT ON CT.customer = C.CustomerId
LEFT JOIN BASE_PaymentTerms PT ON PT.PaymentTermsId = CT.term
LEFT JOIN BASE_Customer_Terms_Comments CTC ON CTC.customer_term = CT.id

WHERE C.IsActive = 1  
AND C.Name Like 'XXXXX%'
AND [distributor] = 'False'
ORDER BY C.Name

Select allOpen in new window

And we need to add buyer and engineer to that as columns.... Is that correct ?

Now I assume you are quite happy with the addresses, country etc, So, I will focus on BASE_Customer C and the two columns Buyer and Engineer. Is that OK ?

If so, then the CTE should be more like :

;with cte_contacts as
( select distinct id_customer, fullname
         , row_number() over (partition by ck_buyer, ck_Engineer order by id_contact) rn
         , case when ck_buyer = 1 then 'Buyer' else 'Engineer' end contact_type
  from   CUSTOMER_Contacts
  where  ck_buyer = 1 or ck_Engineer = 1
), cte_pvt as
( select id_customer, [Buyer],[Engineer] --, id_contact, contact_type
  from   cte_contacts
  pivot
  (max(fullname) for contact_type in ([Buyer],[Engineer])) pvt 
) 
  SELECT C.id_customer, C.[Name], P.Buyer, P.Engineer
  FROM   BASE_Customer C
  OUTER APPLY (select buyer, Engineer from cte_pvt P where p.id_customer = C.id_customer) P(Buyer,Engineer)
  LEFT JOIN Customer_Address CA1 ON CA1.id_customer = C.CustomerId AND CA1.Name = 'Billing'
  LEFT JOIN Customer_Address CA2 ON CA2.id_customer = C.CustomerId AND CA2.Name = 'Shipping'
  LEFT JOIN BASE_country CO1 ON CO1.rowid = CA1.id_country 
  LEFT JOIN BASE_country CO2 ON CO2.rowid = CA2.id_country 
  LEFT JOIN BASE_Customer_Terms CT ON CT.customer = C.CustomerId
  LEFT JOIN BASE_PaymentTerms PT ON PT.PaymentTermsId = CT.term
  LEFT JOIN BASE_Customer_Terms_Comments CTC ON CTC.customer_term = CT.id

  WHERE C.IsActive = 1  
  AND C.Name Like 'XXXXX%'
  AND [distributor] = 'False'
  ORDER BY C.Name
 

Select allOpen in new window

I'm ok with it being reopened.

Question has been re-opened....

Much appreciated - hope it serves you well....

If possible, add in a WHERE clause in the very first part of the CTE to limit the number / volume of data it needs to retrieve from CUSTOMER_Contacts. A little bit concerned that it needs to process data not needed for the query.... Maybe ID_Customer ?

Yes, Id_customer.