oracle关键字pivot行转列【坑爹的三小时，动脑经真累】

现在的位置: 首页 > 综合 > 正文

oracle关键字pivot行转列【坑爹的三小时，动脑经真累】

2017年12月27日 ⁄ 综合 ⁄ 共 889字 ⁄ 字号小中大 ⁄ 评论关闭

首先感谢提供Oracle的行列转换丝路！！！

原始表数据：

辅助思考：

select usertelephone,
  max(decode(filedid, 1, answervalue, 0)) as "问题1",
  max(decode(filedid, 2, answervalue, 0)) as "问题2",
  max(decode(filedid, 3, answervalue, 0)) as "问题3",
  max(decode(filedid, 4, answervalue, 0)) as "问题4",
  max(decode(filedid, 5, answervalue, 0)) as "问题5",
  max(decode(filedid, 6, answervalue, 0)) as "问题6",
  max(decode(filedid, 7, answervalue, 0)) as "问题7"
from t_d_Serveyanswer where serveyid =3 group by usertelephone order by 1 asc;

涨姿势的关键字：pivot

select * from
  (select usertelephone, filedname, answervalue,t.adddate from t_d_Serveyanswer t join
   t_d_Serveyfiled t1 on t.filedid = t1.id where t.serveyid =3)
pivot(
  max(answervalue) for filedname in (
    '坑德基和麦当劳 你更喜欢 吃什么？',
    '你什么？',
    '你是那种人？',
    '性别',
    '你爱好什么？',
    '你叫什么？',
    '你用过天翼客服吗？'
  )
) order by 1;