title: COUNT(DISTINCT) in multiple columns in SQL Server 2008
body: <p>In Oracle, it's possible to get a count of distinct values in multiple columns by using the <strong>||</strong> operator (according to <a href="http://www.dbasupport.com/forums/showthread.php?t=11349" rel="nofollow">this forum post</a>, anyway):</p>
  
  <pre><code>SELECT COUNT(DISTINCT ColumnA || ColumnB) FROM MyTable
  </code></pre>
  
  <p>Is there a way to do this in SQL Server 2008?  I'm trying to perform a single query to return some group statistics, but I can't seem to do it.</p>
  
  <p>For example, here is a table of values I'm trying to query:</p>
  
  <pre><code>AssetId MyId    TheirId   InStock
  328     10      10        1
  328     20      20        0
  328     30      30        0
  328     40      10        0
  328     10      10        0
  328     10      10        0
  328     10      10        0
  328     10      10        0
  </code></pre>
  
  <p>For AssetId #328, I want to compute the total number of unique IDs in the <strong>MyId</strong> and <strong>TheirId</strong> columns (4 = 10, 20, 30, 40), as well as the total number of non-zero rows in the <strong>InStock</strong> column (1):</p>
  
  <pre><code>AssetId     TotalIds    AvailableIds
  328         4           1
  </code></pre>
  
  <p>Is there a way to work this magic somehow?</p>