데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

L5	A1 1 L5 PLC0001 김가나
L4	A1 2 L4 PLC0002 김다라	A2 1 L4 PLC0004 김다라	A3 1 L4 PLC0045 김마바
L4	A1 3 L4 PLC0003 김다라	A2 2 L4 PLC0005 김다라	A3 2 L4 PLC0048 김마바
L4	A1 4 L4 PLC0004 김가나	A2 3 L4 PLC0006 김마바
L3	A1 5 L3 PLC0001 김마바		A3 3 L3 PLC0449 김가나
L3			A3 4 L3 PLC0004 김다라
L3			A3 5 L3 PLC0005 김다라
L3			A3 6 L3 PLC0006 김다라
L2		A2 4 L2 PLC0001 김마바	A3 7 L2 PLC0007 김가나
L2			A3 8 L2 PLC0008 김가나

이렇게 바꾸고 싶습니다.

SELECT 'A1' AS TBNAME, 1 AS SEQ, 'L5' AS GRADE, 'PLC0001' AS ITEMCD, '김가나' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A1' AS TBNAME, 2 AS SEQ, 'L4' AS GRADE, 'PLC0002' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A1' AS TBNAME, 3 AS SEQ, 'L4' AS GRADE, 'PLC0003' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A1' AS TBNAME, 4 AS SEQ, 'L4' AS GRADE, 'PLC0004' AS ITEMCD, '김가나' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A1' AS TBNAME, 5 AS SEQ, 'L3' AS GRADE, 'PLC0001' AS ITEMCD, '김마바' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A2' AS TBNAME, 1 AS SEQ, 'L4' AS GRADE, 'PLC0004' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A2' AS TBNAME, 2 AS SEQ, 'L4' AS GRADE, 'PLC0005' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A2' AS TBNAME, 3 AS SEQ, 'L4' AS GRADE, 'PLC0006' AS ITEMCD, '김마라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A2' AS TBNAME, 4 AS SEQ, 'L2' AS GRADE, 'PLC0001' AS ITEMCD, '김마라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 1 AS SEQ, 'L4' AS GRADE, 'PLC0045' AS ITEMCD, '김마바' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 2 AS SEQ, 'L4' AS GRADE, 'PLC0048' AS ITEMCD, '김마바' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 3 AS SEQ, 'L3' AS GRADE, 'PLC0049' AS ITEMCD, '김가나' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 4 AS SEQ, 'L3' AS GRADE, 'PLC0004' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 5 AS SEQ, 'L3' AS GRADE, 'PLC0005' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 6 AS SEQ, 'L3' AS GRADE, 'PLC0006' AS ITEMCD, '김다라' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 7 AS SEQ, 'L3' AS GRADE, 'PLC0007' AS ITEMCD, '김가나' AS EMPNM FROM DUAL

UNION ALL

SELECT 'A3' AS TBNAME, 8 AS SEQ, 'L2' AS GRADE, 'PLC0008' AS ITEMCD, '김가나' AS EMPNM FROM DUAL

이 글에 대한 댓글이 총 1건 있습니다.

SELECT grade
     , MIN(DECODE(tbname, 'A1', v)) a1
     , MIN(DECODE(tbname, 'A2', v)) a2
     , MIN(DECODE(tbname, 'A3', v)) a3
FROM (SELECT tbname, seq, grade, itemcd, empnm
             , tbname||' '||seq||' '||grade||' '||itemcd||' '||empnm v
             , ROW_NUMBER() OVER(
               PARTITION BY grade, tbname ORDER BY seq) rn
          FROM t
        )
GROUP BY grade, rn
ORDER BY grade DESC, rn
;

마농(manon94)님이 2014-12-08 09:55에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
40638	월별 대출현황 집계 쿼리 질문입니다. [3]	황준석	2014-12-08	6831
40637	열을 행으로 바꾸는 것 질문입니다.ㅠㅠ [1]	신종민	2014-12-08	6799
40636	데이터복사후 삭제 [1]	박명관	2014-12-07	6043
40635	행을열로바꾸기 질문입니다. [1]	김경태	2014-12-06	7375
40634	마지막 로우를 없애고 싶습니다. [2]	김	2014-12-05	6670
40633	oracle ASP 연결 문제 [1]	최창식	2014-12-05	6432
40631	집계함수 그룹함수 물음요 [1]	초보	2014-12-04	6434

작업시간: 0.018초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다