Using TSQL to UNPIVOT simple aggregated totals...is this even possible?

Question

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.

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

This code outputs:

Work  Out  Post
----  ---  ----
  5    3    21

But I would like the output to be displayed like:

Event  Amount
-----  ------
Work   5
Out    3
Post   21

I believe I need to use the UNPIVOT TSQL command, but cant figure out the correct way to use it.

Is this even possible, or am I approaching this problem from the wrong direction?

Answer 1

You don't need to do UNPIVOT, you want aggregation :

select status, count(*)
from CurrentWork 
group by status;

If above data are aggregated then you can use either subuqery or cte with apply :

with t as (
     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
)
select tt.Event, tt.[Amount]
from t cross apply
     ( values ([Work], [Amount]), ([Out], [Amount]), ([Post], [Amount]) 
     ) tt(Event, [Amount]);