How do I convert columns to rows in SQL Server

I have a table like below, there are two address primary and secondary (some EnId has 3 address):

I need to convert it into a row like below:

I try this SQL statement, but it returns random data.

SELECT       EnId, companyAddress, suite,city, state, zip,      phoneNo, fax, emailId, country, primaryAddress  FROM         (SELECT            EnId, Value, field_name       FROM              #ALLdata) src   PIVOT       (MAX(Value)           FOR field_name IN (companyAddress,suite, city.state, zip, PhoneNo,                              fax, emailId, country, primaryAddress)      ) pvt ;   

https://stackoverflow.com/questions/66740747/how-do-i-convert-columns-to-rows-in-sql-server March 22, 2021 at 01:28PM