title: How do I get distinct COUNT in pivot?
body: <p>I have a following table:</p>
  
  <pre><code>State   LAB GROUP   DATE    CODE    ID
  UP  A   I   1-Jan   1   345
  UP  R   S   1-Feb   1   456
  UP  A   S   1-Jan   2   567
  DL  R   S   1-Feb   3   678
  DL  T   S   1-Jan   1   789
  DL  A   S   1-Jan   2   900
  MN  T   S   1-Jan   3   1011
  MN  R   I   1-Feb   1   1122
  MN  S   I   1-Feb   2   1233
  </code></pre>
  
  <p>I need a pivot table of following type:</p>
  
  <pre><code>STATE   A   R   T   TOTAL
  UP  2   1   0   3
  DL  1   1   1   3
  MN  0   1   1   2
  </code></pre>
  
  <p>DISTINCT COUNT OF ID FOR EACH LAB FOR EACH STATE.</p>
  
  <p>I then need the pivot tables filtered for following columns:</p>
  
  <p>GROUP
  DATE
  CODE</p>
  
  <p>So 1st table will have the pivot table above counting only those records which have GROUP=S
  2nd table will have the pivot table above counting only those records which have CODE=1</p>
  
  <p>and so on, I wish to put multiple conditions. and generate several tables one by one and export them.</p>
  
  <p>If this is possible in SQL please let me know! I ruled out excel vba due to the size of table (source table will have 800,000 records approx).</p>