Sql select query combining row and column values

Question

My input table table is as follows:

I need the result as follows:

Please help.

Answer 1

Hi @Hema Ramachandran ,

Welcome to the microsoft TSQL Q&A forum!

Please check:

CREATE TABLE #test(col1 int,col2 varchar(15),col3 varchar(15))  
INSERT INTO #test VALUES(1,'EEB','JOL'),(2,'SNF','SNF')  
                        ,(3,'PLY','PLY'),(4,'SBE','SBE')  
 ,(4,'JDA','JDA'),(5,'JEP','JEP')  
 ,(6,'HVE','HVE'),(7,'KMK','PMA')  
 ,(7,'PMA','SDF'),(7,'SBE','SBE')  
  
  
;WITH cte  
as(SELECT col1,col2 FROM #test  
   UNION ALL  
   SELECT col1,col3 FROM #test)  
  
SELECT * FROM (  
SELECT col1,  
(SELECT DISTINCT col2+';' FROM cte  
  WHERE col1=c.col1  
  FOR XML PATH('')) AS col2  
FROM cte c   
GROUP BY col1  
) t   

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.