title: Select multiple columns from a subquery in SQL Server
body: <p>I know how to select a field from subquery:</p>
  
  <pre><code>SELECT 
      ID, fck, f1, 
      (SELECT f2 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2
  FROM 
      tbl1 T
  </code></pre>
  
  <p>If I need to select two columns from the subquery, actually I do:</p>
  
  <pre><code>SELECT 
      ID, fck, f1, 
      (SELECT f2 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2, 
      (SELECT f3 FROM tbl2 Y WHERE Y.ID = T.fck) AS f3
  FROM 
      tbl1 T
  </code></pre>
  
  <p>There is a way to avoid two subqueries? Something like:</p>
  
  <pre><code>SELECT 
      ID, fck, f1, 
      (SELECT f2, f3 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2, f3
  FROM 
      tbl1 T
  </code></pre>
  
  <p>Database is SQL Server 2008 R2.</p>
  
  <p>I know this simple example may be rewritten using JOINs, but there are real cases where there aren't equivalent forms using JOIN.</p>