动态列扩展的衍生问题

Question

遇到一个动态列转换的问题，原表如下：
JH XCH DBJH DBJH_NUM LTCH LTCH_NUM
a a001 b   2          b005 3
a a001 b   2          b003 1
a a001 b   2          b006 2
a a001 c   1          c001 1
a a001 c   1          c002 2
a a001 d   3          d002 2
a a001 e   4           希望的结果如下：
JH  XCH  DBJH1 LTCH1 LTCH_NUM1 DBJH2 LTCH2 LTCH_NUM2 DBJH3 LTCH3 LTCH_NUM3 DBJH4 LTCH4 LTCH_NUM4
a   a001  c c001 1  b  b003    1     d d003 1   e
a   a001  c c002 2  b  b006    2     d d002 2   e
a   a001  c  b  b005    3     d   e 需求如下：
   原表中按JH和XCH列分组，对于JH的一种取值a来说，DBJH列中的不同取值b、c、d、e  分别按照DBJH_NUM 的值，往右边动态扩展成DBJH1、DBJH2.....最多到DBJH8(即 DBJH_NUM的取值为1到8).
并且每列DBJH都按照对应的LTCH_NUM 关联。
效果如上图。

Answer 1

测试下
select jh,xch,
  max(decode(dbjh_num,1,dbjh))dbjh1,
  max(decode(dbjh_num,1,ltch))ltch1,
  max(decode(dbjh_num,1,ltch_num))ltch_num1,
  max(decode(dbjh_num,2,dbjh))dbjh2,
  max(decode(dbjh_num,2,ltch))ltch2,
  max(decode(dbjh_num,2,ltch_num))ltch_num2,
  max(decode(dbjh_num,3,dbjh))dbjh3,
  max(decode(dbjh_num,3,ltch))ltch3,
  max(decode(dbjh_num,3,ltch_num))ltch_num3,
  max(decode(dbjh_num,4,dbjh))dbjh4,
  max(decode(dbjh_num,4,ltch))ltch4,
  max(decode(dbjh_num,4,ltch_num))ltch_num4,
  max(decode(dbjh_num,5,dbjh))dbjh5,
  max(decode(dbjh_num,5,ltch))ltch5,
  max(decode(dbjh_num,5,ltch_num))ltch_num5,
  max(decode(dbjh_num,6,dbjh))dbjh6,
  max(decode(dbjh_num,6,ltch))ltch6,
  max(decode(dbjh_num,6,ltch_num))ltch_num6,
  max(decode(dbjh_num,7,dbjh))dbjh7,
  max(decode(dbjh_num,7,ltch))ltch7,
  max(decode(dbjh_num,7,ltch_num))ltch_num7,
  max(decode(dbjh_num,8,dbjh))dbjh8,
  max(decode(dbjh_num,8,ltch))ltch8,
  max(decode(dbjh_num,8,ltch_num))ltch_num8
from dynacol
group by jh,xch,nvl(ltch_num,1)
order by jh,xch

Answer 2

照你的要求修改如下
select jh,xch,nvl(ltch_num,1) ltch_num,
  max(max(decode(dbjh_num,1,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh1,
  max(decode(dbjh_num,1,ltch))ltch1,
  max(max(decode(dbjh_num,2,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh2,
  max(decode(dbjh_num,2,ltch))ltch2,
  max(max(decode(dbjh_num,3,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh3,
  max(decode(dbjh_num,3,ltch))ltch3,
  max(max(decode(dbjh_num,4,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh4,
  max(decode(dbjh_num,4,ltch))ltch4,
  max(max(decode(dbjh_num,5,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh5,
  max(decode(dbjh_num,5,ltch))ltch5,
  max(max(decode(dbjh_num,6,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh6,
  max(decode(dbjh_num,6,ltch))ltch6,
  max(max(decode(dbjh_num,7,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh7,
  max(decode(dbjh_num,7,ltch))ltch7,
  max(max(decode(dbjh_num,8,dbjh)))keep(dense_rank first order by nvl(ltch_num,1))over(partition by jh,xch)dbjh8,
  max(decode(dbjh_num,8,ltch))ltch8
from dynacol
group by jh,xch,nvl(ltch_num,1)
order by jh,xch