Is there a way to get the count of all the columns for users?

Hi, DB structure of the source table would really help in figuring out ways to implement grouping mechanism, but beforehand I can say if you plan to use a query entity expression, you might have to 'group by' on two columns(user and role) at-least and use an aggregation function on other columns like id. If you find queryEntity doesn't support grouping on multiple columns(I'm not sure) then you have to see if the same can be done using a todatasubset function(after getting data in raw format with out grouping). Once you are able to get the data in this format you can display it in a grid.

1st approach - The queryEntity am not able to group by more than 2max or am not sure if am missing out something.
2nd approach would bring everything from DB which would be a huge chunk of data on a longer run.
Can we do it somehow using queryEntity is my basic question

How about using a DB view for this ? You can replicate above structure exactly by joining the base table for each role, and then group the view results by user column and role column in the view itself. This also performs better than grouping done at Appian side.

We would/have done this type of data aggregation with the DB view recommended above - create a SQL View to gather data, then create a new CDT w/DS Entity matched to the view. From there, a basic queryEntity will retrieve your data. Works great for us.

Create a DB view using the PIVOT functions and that should give you the required information in the tabular format and you can query the view and display the entries in the grid.