SQL Query help

Question

I have a scenario where we have 2 tables.(Table1 and table2)
Table1

Table2

If i have to do a left join between table1 and table2 based on the columns ParentResourcecode, Resourcecode, Servicecode, Cell
in the output we are getting 8 records as below.

We will consider this as 2 different service and we would require to get the output as below.

Note: here CS1 and CS2 are the child service. Need your help to achieve this.

Answer 1

Hi @Prusty, Parthasarathi (Cognizant)
As venkat answered, you could use ROW_NUMBER() function. However, there need some modification on the PARTITION BY clause.
Please check this query:

SELECT T1.ParentResourcecode,T1.Resourcecode,T1.Servicecode,T1.ChildService  
      ,'CS'+CAST(ROW_NUMBER()OVER(PARTITION BY T1.ParentResourcecode,T1.Resourcecode,T1.Servicecode,T1.ChildService ORDER BY T1.Cell) AS VARCHAR(18)) AS ChildServiceGroup  
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.ParentResourcecode = T2.ParentResourcecode   
                                  AND T1.Resourcecode = T2.Resourcecode   
                                  AND T1.Servicecode = T2.Servicecode   
   AND T1.Cell = T2.Cell   
WHERE T1.Servicecode='PQR'  
ORDER BY ChildServiceGroup,ChildService  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

If we want to group Child Services, we can use Row Number function within each partition.

Select	  
	t1.ParentResourcecode, t1.Resourcecode, t1.Servicecode, t1.ChildService,  
	'CS'+ convert(varchar, (ROW_NUMBER() OVER(   
							partition by t1.ParentResourcecode, t1.Resourcecode, t1.Servicecode   
							order by t1.ChildService)))   
	as ChildServiceGroup  
from Table1 t1 (nolock)  
join Table2 t2 on (nolock)  
	t1.ParentResourcecode = t2.ParentResourcecode   
	and t1.Resourcecode = t2.Resourcecode  
	and t1.Servicecode = t2.Servicecode  
	and t1.Cell = t2.Cell