title: Sorting columns within rows in BigQuery
body: <p>I have table like below  </p>
  
  <pre><code>id  a   b   c    
  1   2   1   3    
  2   3   2   1    
  3   16  14  15   
  4   10  12  13   
  5   15  16  14   
  6   10  12  8    
  </code></pre>
  
  <p>I need to "normalize" this table by sorting values in columns a, b, c - row by row and deduping them counting dups</p>
  
  <p>Expected result  </p>
  
  <pre><code>a   b   c   dups     
  1   2   3   2    
  14  15  16  2    
  10  12  13  1    
  8   10  12  1    
  </code></pre>
  
  <p>I do have solution but I don't see how to "scale" it easily to case when I have more than 3 columns to normalize. The first and last column as you can see below is not an issue. Stuff gets messy for columns in the middle when number of columns > 3 </p>
  
  <pre><code>select a, b, c, count(1) as dups from (
  select a1 as a, if(a != a1 and a != c1, a, if(b != a1 and b != c1, b, c)) as b, c1 as c
  from (select a, b, c, least(a, b, c) as a1, greatest(a, b, c) as c1 from table)
  ) group by a, b, c
  </code></pre>
  
  <p>Can anyone suggest another approach?</p>