SQL Create Modified Record from Condition-CodePudding

I have the following table:

id     activity     xuser     isDone
---------------------------------------
1      abc          tom       y
2      def          tom       n
3      hij          jeff      y
4      klm          jeff      n
5      nop          jeff      n

I want to have the following result:

nCol     tom      jeff
----------------------------
done     1        1
undone   1        2

How can I do that in SQL syntax? Then how can I convert it into linq syntax?
Thank you.

CodePudding user response：

It's much more flexible to return the columns (xuser, donecount, undonecount).

Do a GROUP BY, use case expressions to do conditional aggregation:

select xuser,
       sum(case when isDone = 'y' then 1 else 0 end) as donecount,
       sum(case when isDone = 'n' then 1 else 0 end) as undonecount
from tablename
group by xuser

(Someone else has to do the linq coding.)

CodePudding user response：

If you end up doing something like what @jarlh suggests in his answer, you can achieve a similar SQL result in LINQ through a query more or less like this:

var xUserCounts = 
    from x in tableName
    group x by x.xuser into groupped
    select new 
    {
        User = groupped.Key,
        DoneCount = groupped.Select(y => y.IsDone).Sum(y => y == "y" ? 1 : 0),
        UndoneCount = groupped.Select(y => y.IsDone).Sum(y => y == "n" ? 1 : 0),
    };