title: Concatenate and Remove spaces from 4 columns
body: <p>I need to output 1 column that contains all possible combinations of strings from 4 columns, concatenated and with spaces removed using T-SQL query or Excel formula. </p>
  
  <p><strong>Details:</strong>
  I have a table with 4 rows and 4 columns filled with various strings of different lengths.  I need to output the all possible combinations of strings in each column.  Because I have a 4x4 table, I am expecting 256 different combinations.</p>
  
  <p>Luckily, I can do this with this SQL query:</p>
  
  <pre><code>select
      t1.A,
      t2.B,
      t3.C,
      t4.D
  from 
      table t1 cross join
      table t2 cross join
      table t3 cross join
      table t4
  </code></pre>
  
  <p>However, this gives me an out put with 4 columns.  I need concatenate the results and remove any spaces.</p>
  
  <p>For now, I use the SQL query above, then copy and paste the results into Excel.  From Excel, I would use the TRIM and CONCATENATE functions to get a single column of what I need.</p>
  
  <p>Is there a simple way to do this with a T-SQL query?
  Better yet, is there an Excel formula I can use to do this?  </p>
  
  <p>I have an Excel formula I use, but it only works for 2 columns.  Once I get the combinations from the 2 columns, I copy and paste to another sheet, enter the values of the 3rd column into the new sheet and use the same formula.  Not sure how to extend this to 4 columns.</p>
  
  <pre><code>IF(ROW()-ROW($F$1)+1&gt;COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&amp;INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))
  </code></pre>
  
  <p>Edit:<br>
  Example Table:</p>
  
  <pre><code>A  B  C  D
  -----------
  01 02 03 04
  05 06 07 08
  09 10 11 12
  13 14 15 16
  </code></pre>
  
  <p>Desired Results:</p>
  
  <pre><code>Results
  --------
  01020304
  01020308
  01020312
  01020316
  ........
  13141512
  13141516
  </code></pre>