July 20, 2011 at 1:25 pm
I write a query like this
select A.userid as 'userid',
A.rname as 'Name',
A.isactive as 'Is-Active',`enter code here`
max(case when Group_type=1 then rolename end) as Role1,
max(case when Group_type=2 then rolename end) as Role2,
max(case when Group_type=3 then rolename end) as Role3,
max(case when Group_type=4 then rolename end) as Role4,
max(case when Group_type=5 then rolename end) as Role5,
max(case when Group_type=6 then rolename end) as Role6,
(select count(*)
from (select Rolename,
case RoleName
when 'Admin' then 1
when 'opsjunior'then 2
when 'opssenior' then 3
when 'vendor' then 4
when 'customer' then 5
when 'customerservicerep' then 6
end as group_type
from tbl_roles)x
where x.group_type=a.group_type) as rank
from
(select tu.userid,concat(tu.firstname,tu.lastname) as rname,tr.rolename as 'rolename',tu.emailid,tu.isactive,
case RoleName
when 'Admin' then 1
when 'opssenior' then 2
when 'opsjunior' then 3
when 'vendor' then 4
when 'customer' then 5
when 'customerservicerep'then 6
end as group_type
from tbl_user_details tu
inner join tbl_usersinroles tur on tu.userid=tur.userid
inner join tbl_roles tr on tur.RoleId=tr.RoleId
)A
group by rname
order by userid
The output is like this
user id Name Is-Active Role1 Role2 Role3 Role4 Role5 Role6
1 A 1 admin senior 0 0 0 0 0
2 B 0 0 0 0 0 ops manager junior
3 C 1 admin 0 0 junior 0 0 0
.
.
so on....
But here my requirement is
"The roles are to be added dynamically not as in manual order
Because in my requirement the roles are may increase or decrease"
so please help me out of this issue as early as possible
Thanks in advance!!!
July 21, 2011 at 10:08 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply