title: SQL CASE statement to do multiple thens
body: <p>I am trying to simplify a SQL program which creates unique variables based on the case statement.  So, for example, say I have a data set like the following:</p>
  
  <pre><code>Salesperson   Invoice  ItemCount   Sales   Type
  Joe           1111       100      500.00   internet
  Joe           2222       50       200.00   internet
  Joe           3333       200      750.00   store
  Cthulhu       4444       10       50.00    internet
  Cthulhu       5555       20       100.00   internet
  </code></pre>
  
  <p>And I want to end up with something like:</p>
  
  <pre><code>Salesperson  InternetItems  InternetSales  StoreItems  StoreSales  TotalItems  TotalSales
  Joe             150              700.00      200          750.00     350          1450.00
  Cthulhu          30              150.00      .            .          30           150.00
  </code></pre>
  
  <p>So right now I have something like:</p>
  
  <pre><code>select 
    salesperson, 
    case when Type='internet' then ItemCount else .
      end as InternetItems,
    case when Type='internet' then Sales else .
      end as InternetSales,
  
  ....more code....
  
    sum(ItemCount) as TotalItems,
    sum(Sales) as TotalSales,
   group by 1,2,3....
  </code></pre>
  
  <p>I'd like to be able to condense this by doing something like:</p>
  
  <pre><code>select 
    salesperson, 
    case when Type='internet' then InternetItems=ItemCount and InternetSales=Sales
      else InternetItems=. and InternetSales=.
  </code></pre>
  
  <p>Is there someway to do that when creation of multiple variables is involved?</p>
  
  <p>Also, how would the sum statement work? Is it:</p>
  
  <pre><code>calculated(sum(InternetItems)) as InternetItems
  </code></pre>
  
  <p>Thanks, Pyll</p>