question

HemaRamachandran-9225 avatar image
0 Votes"
HemaRamachandran-9225 asked EchoLiu-msft edited

Sql select query combining row and column values

My input table table is as follows:
117025-image.png

I need the result as follows:
116989-image.png

Please help.


sql-server-transact-sql
image.png (4.9 KiB)
image.png (4.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)?

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @HemaRamachandran-9225,

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:
117008-image.png


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.



image.png (5.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.