학년별로 가공후 중복된 칼럼 이름 안보이게 하기(3가지 풀이법)

--구
select decode(A.COLL,B.COLL,' ',A.COLL) line,
      decode(A.COLL||A.DEPT,
          B.COLL||B.DEPT,' ',A.DEPT)SPEC,
      A.K,A.A
FROM (SELECT ROWNUM CNT1,COLL,DEPT,K,A
      FROM (SELECT COLL
      ,DEPT,DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') K,
      DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) A
FROM TEST11,(SELECT ROWNUM CNT
            FROM USER_TABLES
            WHERE ROWNUM<5)
            ORDER BY DEPT,K)TEST
      )A,
      (SELECT ROWNUM CNT2,COLL,DEPT,K,A
      FROM (SELECT COLL
      ,DEPT,DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') K,
      DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) A
FROM TEST11,(SELECT ROWNUM CNT
            FROM USER_TABLES
            WHERE ROWNUM<5)
            ORDER BY DEPT,K)TEST
      )B
    WHERE  B.CNT2(+)=A.CNT1-1
    ORDER BY A.CNT1;

--범
Select decode(A.coll, B.coll, ' ', A.coll) coll
     , decode(A.coll||A.dept, B.coll||B.dept, ' ', A.dept) dept
     , A.key3
     , A.key4
from
(select cnt1
      , coll
      , dept
      , decode(cnt1, 1, '1학년', 2, '2학년', 3, '3학년', 4, '4학년') key3
      , decode(cnt1, 1, fre, 2, sup, 3, jun, 4, sen) key4
      , row_number() over(order by dept, decode(cnt1, 1, '1학년', 2, '2학년', 3, '3학년', 4, '4학년')) rank
  from test11, (select rownum cnt1
                  from user_tables
                 where rownum < 5
                 )
)A,
(select cnt2
      , coll
      , dept
      , decode(cnt2, 1, '1학년', 2, '2학년', 3, '3학년', 4, '4학년') key3
      , decode(cnt2, 1, fre, 2, sup, 3, jun, 4, sen) key4
      , row_number() over(order by dept, decode(cnt2, 1, '1학년', 2, '2학년', 3, '3학년', 4, '4학년')) rank
  from test11, (select rownum cnt2
                  from user_tables
                 where rownum < 5
                 )
)B
where A.rank-1=B.rank(+)
order by A.rank;

 

--진호
select decode(deptno,dep1,null,deptno) dept
      ,decode(deptno||job,dep1||job1,null,job) job
      ,ename
from (
      select deptno,job
            ,lag(deptno) over(order by deptno,job) dep1
            ,lag(job) over(order by deptno,job) job1
            ,ename
      from emp        
      )
;
select decode(a.deptno,b.deptno,null,a.deptno) deptno
      ,decode(a.deptno||a.job,b.deptno||b.job,null,a.job) job
      ,a.ename
from (select row_number() over(order by deptno,job) num
             ,deptno,job,ename
     from emp)a
    ,(select row_number() over(order by deptno,job) num
            ,deptno,job,ename
    from emp)b
where a.num-1=b.num(+)
order by a.num;