Answered by:
Group by completion

Question
-
User448619955 posted
I have a Table as below:
UserName CompanyName Group Test1 cp1 IT Test1 cp1 HR Test2 cp2 HR how can I write a query to get the result :
UserName CompanyName HasItAceess Test1 cp1 1 Test2 cp2 0 Tuesday, December 8, 2020 2:03 PM
Answers
-
User448619955 posted
I found the solution:
SELECT Username,CompanyName,MAX(HasITAccess) FROM
( SELECT DISTINCT Username,CompanyName, CASE WHEN GroupName='IT' THEN 1 ELSE 0 END AS HasITAccess FROM [Table]) AS result GROUP BY Username,CompanyName- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 9, 2020 10:01 AM
All replies
-
User452040443 posted
Why is the result 1 for Test1/cp1 and 0 for Test2/cp2? What is the criteria?
Tuesday, December 8, 2020 5:53 PM -
User1535942433 posted
Hi vahid.ch,
Accroding to your description,I don't understand your requirment clearly.
What is HasItAceess? Which column is group by? I'm guessing that you need to distinct the username.
Just like this:
select DISTINCT UserName,CompanyName from GT
If this isn't match your requirment,you could post more details to us.
Best regards,
Yijing Sun
Wednesday, December 9, 2020 2:29 AM -
User448619955 posted
thanks for the feedback.
actually I want to bring those users who have access to IT role, it doesn't matter if they have access to another role.
if they have access to IT role, then I want to set a flag in my query with true and if they don't have access to IT role, so accordingly the result will be false.
something like this:
select ....
CASE WHEN Group='IT'
THEN 1 ELSE 0 END AS HasITAccess from ...in this case the result will be:
UserName CompanyName HasItAceess Test1 cp1 1 Test1 cp1 0 Test2 cp2 0 so, I have to remove the second row because Test 1 has access to It role and my requirement will be :
UserName CompanyName HasItAceess Test1 cp1 1 Test2 cp2 0 Wednesday, December 9, 2020 7:56 AM -
User448619955 posted
I found the solution:
SELECT Username,CompanyName,MAX(HasITAccess) FROM
( SELECT DISTINCT Username,CompanyName, CASE WHEN GroupName='IT' THEN 1 ELSE 0 END AS HasITAccess FROM [Table]) AS result GROUP BY Username,CompanyName- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 9, 2020 10:01 AM -
User452040443 posted
You can leave your query as follows:
SELECT Username, CompanyName, MAX(CASE WHEN GroupName = 'IT' THEN 1 ELSE 0 END) AS HasITAccess FROM [Table] GROUP BY Username, CompanyName
Hope this help
Wednesday, December 9, 2020 2:45 PM