?

WITH T AS

?? ?(

?? ?SELECT 'C1' COL1 ,'A1' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C2' COL1 ,'B1' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C2' COL1 ,'B2' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C2' COL1 ,'B3' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C1' COL1 ,'A2' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C1' COL1 ,'A3' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C2' COL1 ,'B4' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C1' COL1 ,'A4' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C1' COL1 ,'A5' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C1' COL1 ,'A6' COL2 FROM DUAL UNION ALL

?? ?SELECT 'C2' COL1 ,'B5' COL2 FROM DUAL

?? ?)

?

?

-- Oracel

?

?ex1:

SELECT   MAX (CASE MOD (CEIL (ROWNUM / CEIL (cnt / 2)) - 1, 2) + 1
              	 WHEN 1
              	 THEN col2
              END) c1
        ,MAX (CASE MOD (CEIL (ROWNUM / CEIL (cnt / 2)) - 1, 2) + 1
                 WHEN 2
                 THEN col2
              END) c2
    FROM (SELECT   ROWNUM
                  ,col1
                  ,col2
                  ,(SELECT COUNT(*) FROM TEST) cnt
              FROM t
          ORDER BY col1) t
GROUP BY MOD (ROWNUM - 1, CEIL (cnt / 2)) + 1 + CEIL (ROWNUM / CNT);

ex2:

maxCount = (select count(*) from t)

SELECT   MAX (CASE MOD (CEIL (ROWNUM / CEIL (cnt / 2)) - 1, 2) + 1
              	 WHEN 1
              	 THEN col2
              END) c1
        ,MAX (CASE MOD (CEIL (ROWNUM / CEIL (cnt / 2)) - 1, 2) + 1
                 WHEN 2
                 THEN col2
              END) c2
    FROM (SELECT   ROWNUM
                  ,col1
                  ,col2
                  ,COUNT(*) OVER(PARTITION BY CEIL(ROWNUM / &maxCount)) CNT
              FROM t
          ORDER BY col1) t
GROUP BY MOD (ROWNUM - 1, CEIL (cnt / 2)) + 1 + CEIL (ROWNUM / CNT);

?

-- MySQL

SET @ROWNUM:=0;

SELECT MAX(CASE MOD(CEIL(ROWNUM / CEIL(CNT / 2)) - 1,2) + 1 WHEN 1 THEN COL2 END) C1,
       MAX(CASE MOD(CEIL(ROWNUM / CEIL(CNT / 2)) - 1,2) + 1 WHEN 2 THEN COL2 END) C2
FROM (
SELECT @ROWNUM:=@ROWNUM + 1 AS ROWNUM,
       COL1,
       COL2,
       (SELECT count(*) FROM T) cnt
  FROM T
 ORDER BY col1
) T1 GROUP BY MOD (ROWNUM - 1,CEIL(CNT/2)) + 1 + CEIL(ROWNUM / (SELECT count(*) FROM T));

?

?

?

?