title: Union of two columns in the same table
body: <p>Say I have the following two columns in the same table</p>
  
  <pre><code>Column 1
  --------
  1
  2
  3
  
  Column 2
  --------
  4
  5
  6
  </code></pre>
  
  <p>How do I get a result which gives me:</p>
  
  <pre><code>Columns
  --------
  1
  2
  3
  4
  5
  6
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>What I'm really looking for is to make sure there is not a more efficient way of typing a union query on multiple columns in the same table without having to repeat which table it is multiple times and repeating a where condition for each union multiple times.</p>
  
  <p>The actual query looks more like this:</p>
  
  <pre><code>WITH T1 AS 
  ( SELECT [Col1] FROM [Table1] 
  )
  SELECT * FROM (
      SELECT [Cols1-100], COUNT(*) as "Count" FROM (
  
          SELECT [Col-1] FROM [Table2] AS [Cols1-100], [T1] 
          WHERE [Table2].[Col-1] = [T1].[Col-1] 
          AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
          UNION ALL
  
          SELECT [Col-2] FROM [Table2] AS [Cols1-100], [T1] 
          WHERE [Table2].[Col-1] = [T1].[Col-1] 
          AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
          UNION ALL
  
          ....................... etc
          ....................... etc
          ....................... etc
          .... etc
  
          SELECT [Col-100] FROM [Table2] AS [Cols1-100], [T1] 
          WHERE [Table2].[Col-1] = [T1].[Col-1] 
          AND [Col-1] != '2' AND [Col-2] != '2' ...... etc .... AND [Col-100] != '2'
  
      ) as [Temp1]
      GROUP BY [Cols1-100]    
  ) as [Temp2]
  </code></pre>
  
  <p>Using @Bohemian Outer Query I can do the following but testing the two query's, it seems a lot slower.</p>
  
  <pre><code>WITH T1 AS 
  ( SELECT [Col1] FROM [Table1] 
  )
  SELECT * FROM (
      SELECT [Cols1-100], COUNT(*) as "Count" FROM (
              SELECT * FROM (
                  SELECT [Col-1] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2] 
                  UNION ALL
                  SELECT [Col-2] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2] 
                  UNION ALL
                  ....................... etc
                  .... etc
                  SELECT [Col-100] AS [Cols1-100], [Col-1], [Col-2], ..... etc ..... [Col-100] FROM [Table2] 
              ) AS SUBQUERY WHERE [Col-1] IN (SELECT [Col1] FROM [T1])
              AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2' 
          ) as [Temp1]
      GROUP BY [Cols1-100]    
  ) as [Temp2]
  </code></pre>