title

sqltest測試:

CREATE TABLE sql_test_a 
( 
    ID         VARCHAR2(4000 BYTE), 
    FIRST_NAME VARCHAR2(200 BYTE), 
    LAST_NAME  VARCHAR2(200 BYTE) 
); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('3', 'Bill', 'Keaton'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('4', 'Greg', 'Mercury'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('5', 'Steve', 'Jobs'); 

語法:

with sql_test_a_rn as (
    select ID,
           FIRST_NAME,
           LAST_NAME,
           mod(rn - 1, 3) as gorup_id,       /* 分組編號 */
           ceil(rn / 3) as newRownum         /* 分組後新的Rownumber */
      from (select ID,
                   FIRST_NAME,
                   LAST_NAME,
                   row_number() over(order by ID) as rn
              from sql_test_a ) t
)
select c1_ID         as ID_1,
       c1_FIRST_NAME as FIRST_NAME_1,
       c1_LAST_NAME  as LAST_NAME_1,
       c2_ID         as ID_2,
       c2_FIRST_NAME as FIRST_NAME_2,
       c2_LAST_NAME  as LAST_NAME_2,
       c3_ID         as ID_3,
       c3_FIRST_NAME as FIRST_NAME_3,
       c3_LAST_NAME  as LAST_NAME_3
 from sql_test_a_rn
 pivot (
    max(ID) as ID, max(FIRST_NAME) as FIRST_NAME, max(LAST_NAME) as LAST_NAME
    for gorup_id in
    (
        0 as c1, 
        1 as c2, 
        2 as c3
    )
 )
order by newRownum