CODEWITHSUNDEEP
mysqlsqlsql-server
KP.
KP.

Reputation: 393

query to segregate users based on role per project in mysql

I have a table which contains projectID, UserID and RoleID along with other columns.

I trying to get a query that will give me the users based on role for each project.

I tried if and case statements but was able to address this.

table: 

projectid   | userid | roleid | flag 
1000001     | 20001  | 1      | Y    
1000001     | 20002  | 2      | Y    
1000001     | 20003  | 2      | Y   
1000001     | 20004  | 3      | Y    
1000001     | 20005  | 1      | Y   
1000002     | 20006  | 3      | Y

consider role 1 = junior, 2 = TL, 3 = HOD, I'm trying to get something like this

projectid | junior       | TL           | HOD 
1000001   | 20001, 20005 | 20002, 20003 | 20004
1000002   | -------------| -------------| 20006

Upvotes: 3

Views: 178

Answers (1)

G one
G one

Reputation: 2729

You need to use group_concat

select projectid,
group_concat(case when roleid = 1 then userid end) junior,
group_concat(case when roleid = 2 then userid end) senior,
group_concat(case when roleid = 3 then userid end) TL
from table1
group by projectid

fiddle

Upvotes: 6

Related Questions