Saturday, May 9, 2009

การแสดงผลแบบ 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

9 comments:

Anonymous said...

select type1,type2,count(*)
from t1 group by type1,type2;

ค่าได้มาดังนี้ (โดยการ group)
type1 type2 count(*) มีค่า
M 0 56
M 1 472
M 2 47
R 0 1
R 1 2
R 2 1

ต้องการให้โชว์เพียง 1 Row โดยเลือกเฉพาะค่าที่ count(*) ดังนี้

56 472 47 1 2 1

จะต้องทำอย่างไร

Tanakorn Tavornsasnavong said...

SQL> select max(sys_connect_by_path(cnt,',')) cnt from
2 (select rownum rn, cnt from
3 (
4 select count(*) cnt from t1 group by type1, type2
5 ))
6 start with rn = 1
7 connect by prior rn = rn-1;

CNT
------------------------------
,1,2,1,1

เราอาจจะใช้ฟังก์ชั่น Substr หรืออื่น ๆ ในการตัดเอาคอมมาตัวแรกออกก็ได้ครับ

Anonymous said...

oracle มันสุดยอดมากครับ ไม่รู้ว่า mysql ทำได้เปล่า

Unknown said...

MySQL ก็ทำได้ครับ
SELECT
DISTINCT(CONCAT(name,type)) AS unique_column, GROUP_CONCAT(class SEPARATOR ', ')
FROM tab
GROUP BY unique_column

Tanakorn Tavornsasnavong said...

ขอบคุณครับ ที่ร่วมกันแชร์ คุณ iMishell เขียน Application บน MySQL หรือครับ?

Anonymous said...

SELECT ROWNUM SEQ, x.brh , x.dept ,x.itmgrp, x.usecost
(
select a.brh , a.dept , a.itmgrp , sum(b.usecost) as usecost
from issue a , issue_line b
where a.doc_no = b.doc-no
group by a.brh , a.dept , a.itmgrp
order by sum(b.usecost) desc
) x
WHERE ROWNUM <= NVL(:LEVEL,999)

ผลลัพธ์ที่ได้ คือ
brh dept itmgrp usecost
B01 D01 I003 500
B01 D01 I001 400
B01 D01 I002 300
B01 D02 I003 700
B01 D02 I001 600
B01 D02 I002 500
B02 D01 I003 600
B02 D01 I001 500
B02 D01 I002 400
break group in report โดยจัดเรียง usecost มากไปน้อย ยังผิดใน group brh , dept อ่าคะ^^!
B01 3,000*เรียงผิด*
D01 1,200*เรียงผิด*
I003 500
I001 400
I002 300
D02 1,800*เรียงผิด*
I003 700
I001 600
I002 500
B02 3,600*เรียงผิด*
D01 1,500*เรียงผิด*
I003 600
I001 500
I002 400
D02 2,100*เรียงผิด*
I003 800
I001 700
I002 600
รบกวนชี้แนะด้วยคะ ขอบคุณคะ

จินตนา แสงดี said...

ขอบคุณสำหรับการแชร์ความรู้ดีๆๆค่ะ

Unknown said...

รบกวนสอบถามครับ

Unknown said...

จำนวน ร้อยละ
1=พรรคเพื่อไทย 793 22.9
2=พรรคประชาธิปัตยื 854 24.7
3=พรรคชาติไทยพัฒนา 75 2.2
4=พรรคภูมิใจไทย 27 0.8

1=พรรคเพื่อไทย 2=พรรคประชาธิปัตยื 3=พรรคชาติไทยพัฒนา 4=พรรคภูมิใจไทย
รวม 793 854 75 27
กรุงเทพฯ 90 138 3 1
ปริมณฑล 62 74 7 4
ภาคกลาง 119 125 28 2
ภาคตะวันออก 52 90 3 1
ภาคตะวันออกเฉียงเหนือ 272 122 21 10
ภาคเหนือ 177 102 9 9
ภาคใต้ 20 199 4 0

ต้องการจะแสดงผลแบบนี้ครับ
ส่วน code

include("connect_db.php");
$table_name = "caucus1";



$totalrecord = mysql_num_rows($result);
$allrecord=$totalrecord;
$pagesize = 30;
$totalpage = (int ) ($totalrecord / $pagesize);

if (($totalrecord % $pagesize) !=0)
{ $totalpage+=1; }

$pageid = $_GET['pageid'];
if (isset($pageid))
{ $start = $pagesize * ($pageid-1); }
else
{ $pageid =1;
$start = 0;
}
$sql = "SELECT geography,cau, count(*) As count
FROM $table_name
WHERE cau is not null
GROUP BY geography,cau;";

$result = mysql_query($sql);
?>




" ?>


ผลที่แสดงหน้าเว็บครับ

กรุงเทพมหานคร 122 
กรุงเทพมหานคร 176 
กรุงเทพมหานคร 4 
กรุงเทพมหานคร 2 
ปริมณฑล 76 
ปริมณฑล 90 
ปริมณฑล 7 
ปริมณฑล 4 
ภาคกลาง 164 
ภาคกลาง 170 
ภาคกลาง 32 
ภาคกลาง 4 
ภาคตะวันออก 78 
ภาคตะวันออก 117 
ภาคตะวันออก 4 
ภาคตะวันออก 2 
ภาคตะวันออกเฉียงเหนือ 339 
ภาคตะวันออกเฉียงเหนือ 172 
ภาคตะวันออกเฉียงเหนือ 23 
ภาคตะวันออกเฉียงเหนือ 11 
ภาคเหนือ 229 
ภาคเหนือ 145 
ภาคเหนือ 10 
ภาคเหนือ 9 
ภาคใต้ 37 
ภาคใต้ 274 
ภาคใต้ 4 
ภาคใต้ 2 

Post a Comment