title: How to create dynamic dummy variables in SQL
body: <p>I have a simple table with 2 columns: ID (integer) and Category (string), and each ID can repeat with a few categories, like so:</p>
  
  <pre><code>ID    Cat
  ---   ---
  1     A
  1     B
  2     B
  3     A
  3     B
  3     C
  </code></pre>
  
  <p>I want to reshape this table so that each unique Category would be a dummy variable (0/1 if ID has it):</p>
  
  <pre><code>ID    A   B   C
  ---   --  --  --
  1     1   1   0
  2     0   1   0
  3     1   1   1
  </code></pre>
  
  <p>Now, if the set of unique categories is known (and small) this is an easy CASE WHEN statement x no. of unique categories.</p>
  
  <p>My questions are:</p>
  
  <p>a) What if it isn't unknown or really large? How do I create this 'CASE WHEN' effect automatically?</p>
  
  <p>b) More importantly: I'm not necessarily interested in all categories (say only dummies for 'A' and 'B') but only categories which I have in a separate table called Cats, which is a simple 1 column holding my relevant categories (again, unknown how many), like:</p>
  
  <pre><code> Cat
   ---
   A
   B
  </code></pre>
  
  <p>How do I create dummy variables for only the categories in this dynamic table?</p>
  
  <p>Do you think all of this should really be done in other tools e.g. R?</p>
  
  <p>Thanks!</p>
  
  <p>(I'm using Teradata SQL with SQLA, but I think it's a general SQL question)</p>