WITH TB_TEST
AS
(
  select 'col1' col, '홍길동' name from dual 
  union all
  select 'col1' col, '강감찬' name from dual 
  union all
  select 'col1' col, '김부식' name from dual 
  union all
  select 'col1' col, '김동수' name from dual 
  union all
  select 'col2' col, '둘리' name from dual 
  union all
  select 'col2' col, '또치' name from dual 
  union all
  select 'col3' col, '김춘삼' name from dual 
  union all
  select 'col3' col, '김동식' name from dual 
  union all
  select 'col3' col, '강동원' name from dual 
  union all
  select 'col4' col, '이요원' name from dual 
  union all
  select 'col4' col, '김태희' name from dual 
  union all
  select 'col4' col, '김수지' name from dual
)
-- 첫번째 쿼리
SELECT MIN(DECODE(COL_NO, 1, COL , NULL)) AS COL1
   , MIN(DECODE(COL_NO, 1, NAME, NULL)) AS NAME1
   , MIN(DECODE(COL_NO, 2, COL , NULL)) AS COL2
   , MIN(DECODE(COL_NO, 2, NAME, NULL)) AS NAME2
FROM  (SELECT TB_TEST.*
        , RANK() OVER (PARTITION BY COL ORDER BY ROWNUM) AS RNUM
        , 2-MOD(SUBSTR(COL,4,1),2) AS COL_NO
        , DECODE(MOD(SUBSTR(COL,4,1),2),1, SUBSTR(COL,4,1), SUBSTR(COL,4,1) - 1) AS GRP_NO
     FROM  TB_TEST
     ORDER BY ROWNUM)
GROUP BY GRP_NO,RNUM

UNION ALL
SELECT '' AS COL1
   , '(구' AS NAME1
   , '분)' AS COL2
   , '' AS NAME2
FROM  DUAL
UNION ALL

-- 두번째 쿼리
SELECT MIN(DECODE(COL_NO, 1, COL , NULL)) AS COL1
   , MIN(DECODE(COL_NO, 1, NAME, NULL)) AS NAME1
   , MIN(DECODE(COL_NO, 2, COL , NULL)) AS COL2
   , MIN(DECODE(COL_NO, 2, NAME, NULL)) AS NAME2
FROM  (SELECT TB_TEST.*
        , RANK() OVER (PARTITION BY MOD(SUBSTR(COL,4,1),2) ORDER BY ROWNUM) AS RNUM
        , 2-MOD(SUBSTR(COL,4,1),2) AS COL_NO
        , DECODE(MOD(SUBSTR(COL,4,1),2),1, SUBSTR(COL,4,1), SUBSTR(COL,4,1) - 1) AS GRP_NO
     FROM  TB_TEST
     ORDER BY ROWNUM)
GROUP BY RNUM

;

뉴ㅅ뉴)ㆀ 회의 끝나고 오니 풀이가... 있군요. 그래도 풀어봐야지... + ㅁ +)/

--1번
WITH T AS (
  SELECT
    ROUND(DENSE_RANK() OVER(ORDER BY col) / 2) AS group_no,
    ROW_NUMBER() OVER(PARTITION BY col ORDER BY col) AS local_seq_no,
    MOD(DENSE_RANK() OVER(ORDER BY col), 2) AS pos_no,
    col,
    name
  FROM (
    SELECT 'col1' col, '홍길동' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '강감찬' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '김부식' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '김동수' name FROM DUAL
    UNION ALL
    SELECT 'col2' col, '둘리' name FROM DUAL
    UNION ALL
    SELECT 'col2' col, '또치' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '김춘삼' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '김동식' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '강동원' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '이요원' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '김태희' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '김수지' name FROM DUAL
  )
)
SELECT
  MAX(col1) AS col1,
  MAX(name1) AS name1,
  MAX(col2) AS col2,
  MAX(name2) AS name2
FROM (
  SELECT
    group_no, local_seq_no,
    DECODE(pos_no, 1, col) AS col1,
    DECODE(pos_no, 1, name) AS name1,
    DECODE(pos_no, 0, col) AS col2,
    DECODE(pos_no, 0, name) AS name2
  FROM
    T
) 
GROUP BY
  group_no, local_seq_no
ORDER BY
  group_no, local_seq_no

--2번
WITH T AS (
  SELECT
    ROWNUM AS rn,
    MOD(DENSE_RANK() OVER(ORDER BY col), 2) AS pos_no,
    col,
    name
  FROM (
    SELECT 'col1' col, '홍길동' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '강감찬' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '김부식' name FROM DUAL
    UNION ALL
    SELECT 'col1' col, '김동수' name FROM DUAL
    UNION ALL
    SELECT 'col2' col, '둘리' name FROM DUAL
    UNION ALL
    SELECT 'col2' col, '또치' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '김춘삼' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '김동식' name FROM DUAL
    UNION ALL
    SELECT 'col3' col, '강동원' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '이요원' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '김태희' name FROM DUAL
    UNION ALL
    SELECT 'col4' col, '김수지' name FROM DUAL
  )
)
SELECT
  pn,
  MAX(col1) AS col1,
  MAX(name1) AS name1,
  MAX(col2) AS col2,
  MAX(name2) AS name2
FROM (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY pos_no ORDER BY rn) AS pn,
    DECODE(pos_no, 1, col) AS col1,
    DECODE(pos_no, 1, name) AS name1,
    DECODE(pos_no, 0, col) AS col2,
    DECODE(pos_no, 0, name) AS name2
  FROM
    T
)
GROUP BY
  pn