How to count in multiple columns a variable

Question

I am working on a Crystal Report and cannot figure out a way to do this in Crystal or MSSQL

I have a table with usernames that get recorded when an activity is completed in a web app like below

UID Approved    Denied  Moved   Pended
1   Tom         null    null    Bill
2   null        null    Bill    null
3   Bill        null    Tom     null
4   null        Tom     null    null
5   Tom         null    Bill    Bill
6   null        Bill    Bill    null
7   Tom         null    null    Bill

I need to produce a productivity report like below

        Approved    Denied  Moved   Pended
Tom     3           1       1       0
Bill    1           1       3       3

I am not sure how to group the counts by the names that are in the data?

Answer 1

you can also use pivot and unpivot syntax like below: See working demo

select 
Name,
[approved]=ISNULL([approved],0),
[denied]=ISNULL([denied],0),
[moved]=ISNULL([moved],0),
[pending]=ISNULL([pending],0) 
from
(
    select 
      Name,activity,count(1) c 
    from
    (select * from useractivity )s
    unpivot (Name for activity in ([approved],[denied],[moved],[pending]))up
    group by Name,activity
) s
pivot (sum(c) for activity in ([approved],[denied],[moved],[pending]))p