row_number() [ms sql2005 부터 가능. 오라클은 8i 부터 가능]
create table tmptest ( a int, b int, c int, d int ) begin insert into tmptest values(1, 0, 0, 0); insert into tmptest values(0, 7, 0, 0); insert into tmptest values(0, 12, 0, 0); insert into tmptest values(0, 30, 0, 0); insert into tmptest values(0, 0, 9, 0); insert into tmptest values(0, 0, 10, 0); insert into tmptest values(0, 0, 12, 0); insert into tmptest values(0, 0, 21, 0); insert into tmp..
2007. 6. 28.
행을 열로 바꾸기. with as( ~ )
뚤뚤~~~ refresh ~, renew ~ WITH T AS( SELECT '3234' A,'태희' B FROM DUAL UNION ALL SELECT '7777', '양민' FROM DUAL UNION ALL SELECT '0792', '수애' FROM DUAL UNION ALL SELECT '2345', '지현' FROM DUAL UNION ALL SELECT '2345', '영애' FROM DUAL UNION ALL SELECT '2345', '수진' FROM DUAL ) SELECT T.A ,MAX(SYS_CONNECT_BY_PATH(T.B,'||')) B FROM ( SELECT T.A ,T.B ,COUNT(T.A) OVER (PARTITION BY T.A) CNT ,ROWNUM RN FROM..
2006. 5. 21.