title: Display only values that are greater than one in an sql query that uses pivot tables in a CTE
body: <p>I have a query that produces a number of columns, most of them containing 0.  I want to only display the columns that have a value > 0.  This query is in a CTE, and pivoted so as to produce one row.  My query is as follows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>   select [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]
  
  from (select inv.statusreason
  
  from invBillTBL inv WITH (NOLOCK)
       INNER JOIN CarrierTBL car WITH (NOLOCK) On inv.Carrier_Key = Car.Carrier_Key
  
  where inv.ClientLoc_Key IN (
             select ClientLoc_key from clientloctbl WITH (NOLOCK)
             where Client_Key = 286)
      and inv.invEnteredDate &gt;= '2013-11-08' and inv.invEnteredDate &lt; '2013-11-14'
      and car.SCAC = 'DNZA'
      and inv.statusreason &lt;&gt; '' ) a
  
  pivot
  (count(statusreason)  
  for statusreason in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])) p `
  </code></pre>
  
  <p>The output looks like this (shortened version): <img src="https://i.stack.imgur.com/vuxnV.jpg" alt="enter image description here">
  The full row goes out to the end of the statusreasons listed above.</p>
  
  <p><img src="https://i.stack.imgur.com/vuxnV.jpg" alt="enter image description here"></p>
  
  <p>Most of the columns have 0 in the field (along with their count).  I want to only display the columns with counts > 0.  This question has been difficult to formulate, so if I have missed something or left something out, please let me know.  Thanks in advance for any assistance anyone can provide.</p>