행을 열로 바꿔보자.

tbldept :

dept              |        payDate

영업부           |        2008-03-11

영업부           |        2007-02-21

영업부           |        2006-10-04

영업부           |        2002-07-31

총무부           |        2004-11-27

총무부           |        2005-01-07

.

.

.

 

2. 요구사항.

QUERY : SELECT ....

RESULT :

영업부         |       2002-07-31    |      2006-10-04       |       2007-02-21      |    2008-03-11

총무부         |       2004-11-27    |      2005-01-07       |       2007-06-22      |    2007-08-07

개발부         |       2003-02-05    |      2004-04-29       |       2005-05-31      |    2007-08-15

.

.

.

 

 

MS-SQL 에서 사용된 쿼리문입니다

ORACLE 도 비슷하게 사용할 수 있습니다.

 

select dept
, max(payDate1) payDate1
, max(payDate2) payDate2
, max(payDate3) payDate3
, max(payDate4) payDate4
FROM (
  select dept
  , case ROW_NO when 1 then payDate else null end as payDate1
  , case ROW_NO when 2 then payDate else null end as payDate2
  , case ROW_NO when 3 then payDate else null end as payDate3
  , case ROW_NO when 4 then payDate else null end as payDate4
  FROM (
     SELECT 
     row_number() over(ORDER BY juminno asc) as  'ROW_NO'
     , dept , payDate 
     FROM tbldept
  ) A
) B
group by dept