Just curious , no business need. I was able to implement using CTE, but wanted to see if there was any ingenious solution to do it.

A case might be custom reporting where an user can drag columns from different tables to one report. Each column on the report is a select from a different table.

Thanks All