title: SQL query, store result of SELECT in local variable
body: <p>I create a query with some results reused. I search a way to put the result into a variable and use it.</p>
  
  <p>A simple way to see what I want something looking like this - I want this:</p>
  
  <pre><code>DECLARE @result1 ?????
  
  SET @result1 = SELECT a,b,c FROM table1
  SELECT a AS val FROM @result1
  UNION
  SELECT b AS val FROM @result1
  UNION
  SELECT c AS val FROM @result1
  </code></pre>
  
  <p>Not this :</p>
  
  <pre><code> SELECT a AS val FROM (SELECT a,b,c FROM table1)
   UNION
   SELECT b AS val FROM (SELECT a,b,c FROM table1)
   UNION
   SELECT c AS val FROM (SELECT a,b,c FROM table1)
  </code></pre>
  
  <p>It's not the result of this query that I'm concerned with, but instead:</p>
  
  <ol>
  <li><p>to stop selecting the result so many times - in my sample, I reselected the table 3 times</p></li>
  <li><p>the query of <code>@result1</code> is usually so much more complex. So, with a variable, the code will be cleaner.</p></li>
  </ol>
  
  <p>Maybe I want to much - or there's a type of local variable. Or using the type table and set data inside.</p>
  
  <p>What do you suggest me?</p>
  
  <p>Thank you</p>