Someone else tried to add to his answer with this edit
Edit: The T-SQL (SQL Server) syntax uses CROSS APPLY to perform the same task, i.e.: SELECT x.name, y.address FROM temp x CROSS APPLY ( VALUES (x.name, x.city), (x.name, x.phone_no), (x.name, x.pincode)) AS y(name, address) CROSS APPLY in SQL Server also often generates more effective execution plans than a bunch of union operators, so it's definitely worth a try.
I had rejected it with the reason
Should be posted as a separate answer.
Would it be worth it to add this as a standard reason for rejecting edits ?