title: Using TSQL to UNPIVOT simple aggregated totals...is this even possible?
body: <p>I was trying to perform a simple un-pivoting of some data before it is loaded into Microsoft PowerBI.  Since the PowerBI report has to use DirectQuery, using 'Unpivot' in the query editor is not an option.  So it seemed that this could probably be done in the intial SQL that gets loaded.</p>
  
  <pre><code>select
       sum(case when cw.State = 'WORK' then 1 else null end) [Work]
      ,sum(case when cw.State = 'OUT' then 1 else null end)  [Out]
      ,sum(case when cw.State = 'POST' then 1 else null end) [Post]
  from CurrentWork cw
  </code></pre>
  
  <p>This code outputs:</p>
  
  <pre><code>Work  Out  Post
  ----  ---  ----
    5    3    21
  </code></pre>
  
  <p>But I would like the output to be displayed like:</p>
  
  <pre><code>Event  Amount
  -----  ------
  Work   5
  Out    3
  Post   21
  </code></pre>
  
  <p>I believe I need to use the UNPIVOT TSQL command, but cant figure out the correct way to use it.</p>
  
  <p>Is this even possible, or am I approaching this problem from the wrong direction?</p>