การแสดงผลแบบ Transpose ข้อมูลจาก Row มาเป็น Column

Update: 8/03/2009

ในบางครั้งเราอาจจะต้องการแสดงผลข้อมูลที่อยู่ในรูปแถวแนวตั้ง ให้มาแสดงผลในรูปแนวนอนแทน ดังตัวอย่างต่อไปนี้

SQL> select deptno, ename from emp order by deptno;

DEPTNO ENAME
10 CLARK
10 KING
10 MILLER
20 JONES
20 FORD
20 ADAMS
20 SCOTT
30 ALLEN
30 JAMES
30 TURNER
30 MARTIN
30 BLAKE
30 WARD

ถ้าหากคุณต้องการแสดงผลแบบให้ ENAME ที่อยู่ใน DEPTNO เดียวกันขึ้นมาอยู่บน row เดียวกัน ดังนี้
DEPTNO ENAME
10 ,CLARK,KING,MILLER
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

โดยใช้คำสั่งดังต่อไปนี้
SQL> SELECT DEPTNO, MAX(SYS_CONNECT_BY_PATH(ENAME, ',')) ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
GROUP BY DEPTNO

โดย ROW_NUMBER() เป็น function ที่จะสร้าง pseudo column เป็นค่าอันดับคล้าย ๆ ROWNUM แต่ ROW_NUMBER จะเริ่มนับ 1 ใหม่ทุกครั้งที่ค่าของคอลัมน์ที่ต้องการ partition เปลี่ยนไป เช่นถ้าคิวรี
SQL> (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
จะได้ว่า
DEPTNO ENAME RN
10 CLARK 1
10 KING 2
10 MILLER 3
20 ADAMS 1
20 FORD 2
20 JONES 3
20 SCOTT 4
30 ALLEN 1
30 BLAKE 2
30 JAMES 3
30 MARTIN 4
30 TURNER 5
30 WARD 6

ฟังก์ชั่น SYS_CONNECT_BY_PATH , START WITH และ CONNECT BY เป็นการเอาข้อมูลจากในฟิลด์ ENAME มาต่อกันโดยใช้คอมมา (‘,’) เป็นตัวคั่น (SYS_CONNECT_BY_PATH(COLNAME,’,’) โดยเอาค่าจากคอลัมน์นี้ในแถวปัจจุบันมาเชื่อมกับแถวก่อนหน้า (CONNECT BY PRIOR RN = RN-1 AND PRIOR DEPTNO = DEPTNO) โดยเริ่มที่ row แรก (START WITH RN = 1)
จะได้คำสั่งเป็น
SQL> SELECT DEPTNO, SYS_CONNECT_BY_PATH(ENAME, ',') ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO

DEPTNO ENAME
10 ,CLARK
10 ,CLARK,KING
10 ,CLARK,KING,MILLER
20 ,ADAMS
20 ,ADAMS,FORD
20 ,ADAMS,FORD,JONES
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN
30 ,ALLEN,BLAKE
30 ,ALLEN,BLAKE,JAMES
30 ,ALLEN,BLAKE,JAMES,MARTIN
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

จะเห็นได้ว่า ENAME จะมาเชื่อมต่อ (connect) กับ row ก่อนหน้าไปเรื่อย ๆ จนหมดทุก row ในแต่ละ DEPTNO สังเกตว่า row สุดท้ายของแต่ละ DEPTNO จะมีข้อมูลครบถ้วนเนื่องจากเอาข้อมูลมารวมกันทั้งหมดตั้งแต่ row แรกถึง row สุดท้าย เราจึงต้องการเฉพาะ row สุดท้าย row เดียว เราจึงจะใช้ function MAX มาตัดเอา ENAME ที่ไม่ต้องการออกไป
SQL> SELECT DEPTNO, MAX(SYS_CONNECT_BY_PATH(ENAME, ',')) ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
GROUP BY DEPTNO
จะได้ข้อมูลสุดท้ายดังนี้
DEPTNO ENAME
10 ,CLARK,KING,MILLER
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD