列传行sql

1 子查询方法
select u.userid ,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=1) as jbr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=2) as fhr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=3) as shr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=4) as yjsc,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=5) as yjfp
from
table(multiset(
select userid  from view_user_juese group by userid
)) u

2  连接方法
select u.userid,v1.jueseid,v2.jueseid,v3.jueseid,v4.jueseid,v5.jueseid
from
table(multiset(
select userid,''  from view_user_juese group by userid
)) u
left join view_user_juese v1 on v1.userid=u.userid and v1.jueseid=1
left join view_user_juese v2 on v2.userid=u.userid and v2.jueseid=2
left join view_user_juese v3 on v3.userid=u.userid and v3.jueseid=3
left join view_user_juese v4 on v4.userid=u.userid and v4.jueseid=4
left join view_user_juese v5 on v5.userid=u.userid and v5.jueseid=5
3  case when
select userid,
 max(case when jueseid=='1' then '1'
 else '0' end ) jb ,
 max(case
 when jueseid=='2' then '1'
 else '0' end) sh ,
 max(case
 when jueseid=='3' then '1'
 else '0'  end) fh ,
 max(case
 when jueseid=='4' then '1'
 else '0' end) yjfs ,
 max(case
 when jueseid=='5' then '1' --where userid='c3'
 else '0'  end) yjsc    已创建
from view_user_juese  group by userid

一个人可能有多个角色列转行：
查询出结果如：
userid   jb  fh sh fs  sc
1            1      2          4
2            1             3
3            1                  4
4                   2