title: MongoDB transforming data using aggregation
body: <p>I have the following data in a collection.</p>
  
  <pre><code>[
      {name: "a", status: 1},
      {name: "a", status: 2},
      {name: "a", status: 3},
      {name: "b", status: 1},
      {name: "b", status: 4},
      {name: "c", status: 1},
      {name: "c", status: 1},
      {name: "c", status: 5}
  ]
  </code></pre>
  
  <p>I want it to be transformed as:</p>
  
  <pre><code>{
      names: ["a", "b", "c"],
      statuses: [1, 2, 3, 4, 5],
      count: [
          [1, 1, 1, 0, 0],
          [1, 0, 0, 1, 0],
          [2, 0, 0, 0, 1]
      ]
  }
  </code></pre>
  
  <p>Explanation:</p>
  
  <ol>
  <li>names will have the unique list of name attribute</li>
  <li>statuses will have the unique list of status attribute</li>
  <li>Count contains array of count of status per name
  
  <ol>
  <li>For example the first count array is [1, 1, 1, 0, 0]</li>
  <li>Which means:
  
  <ol>
  <li>There is only one status:1 for the name a</li>
  <li>There is only one status:2 for the name a and so on..</li>
  </ol></li>
  <li>If you take the 3rd array [2, 0, 0, 0, 1]
  
  <ol>
  <li>The name c has status:1 two times</li>
  <li>There is no 2 or 3 or 4 but it has one 5</li>
  </ol></li>
  </ol></li>
  </ol>
  
  <p><b>Can you suggest the corresponding aggregation function or equivalent?</b></p>
  
  <p>Edit:
  I just have this so far:</p>
  
  <pre><code>db.collection1.aggregate([
    {
      $group: {
        _id: {environment: "$name", status: "$status"}, 
        count:{$sum: 1}
      }
    }
  ])
  </code></pre>
  
  <p>Thank you</p>