title: Count and filter with Group By; and Rows to Columns conversion
body: <p>I have two Tables</p>
  <ol>
  <li>Task</li>
  <li>TaskDetail</li>
  </ol>
  <p>Task:</p>
  <pre><code>| TaskName |Department|TaskStatus|DetailID  |
  |--------- |--------- |--------- |--------- |
  | T1       | D1       | 31       | 1        |
  | T1       | D2       | 32       | 2        |
  | T1       | D3       | 31       | 3        |
  </code></pre>
  <ul>
  <li><p>Task 【T1】 is assigned to three departments 【D1, D2, D3】</p>
  </li>
  <li><p>Each department has a different DeatilID for each task.</p>
  </li>
  <li><p>there can be at most three TaskStatus: 30,31,32</p>
  </li>
  </ul>
  <p>TaskDetail:</p>
  <pre><code>|DetailID | AssetCode    | CodeStatus    |
  |-------  |------------  |------------   |
  | 1       | AC1          | 14            |
  | 1       | AC2          | 14            |
  | 1       | AC3          | 15            |
  | 1       | AC4          | 16            |
  | 1       | AC5          | 14            |
  | 1       | AC6          | 16            |
  | 2       | AC7          | 16            |
  | 2       | AC8          | 16            |
  | 2       | AC9          | 16            |
  | 2       | AC10         | 16            |
  | 2       | AC11         | 16            |
  | 3       | AC10         | 15            |
  | 3       | AC11         | 14            |
  </code></pre>
  <ul>
  <li>TaskDetail has different asset codes for each department</li>
  <li>There can be at most three CodeStatus: 14, 15, 16</li>
  </ul>
  <p>Desired Results:</p>
  <pre><code>| Row_ID   | TaskName |Department|TaskStatus|DetailID   |Count(14) |Count(15) |Count(16) |
  |--------- |--------- |--------- |--------- |--------- |--------- |--------- |--------- |
  | 1        | T1       | D1       | 31       | 1        | 3        | 1        | 2        |
  | 2        | T1       | D2       | 30       | 2        | 0        | 0        | 5        |
  | 3        | T1       | D3       | 32       | 3        | 0        | 1        | 1        |
  </code></pre>
  <ul>
  <li><p>TaskStatus is based on the CodeStatuses in TaskDetail Table</p>
  <pre><code>  if(count(16) = all) then TaskStatus 30;                 //as in Row_ID 2
    if(count(14)&gt;=0 || count(15)&gt;=0) then TaskStatus 31;    //as in Row_ID 1
    if(count(16)==0) then TaskStatus 32;                    //as in Row_ID 3
  </code></pre>
  </li>
  <li><p>The same CodeStatus are counted for each department based on the TaskName and placed in the columns Count(14), Count(15), and Count(16). For instance:</p>
  <pre><code>  Count(14)= Count of all CodeStatus = 14 
    Count(15)= Count of all CodeStatus = 15 
    Count(16)= Count of all CodeStatus = 16 
  </code></pre>
  </li>
  </ul>