[MySql] Rows to Columns 팁

sum

이렇게 생긴 테이블을

테이블명 : HISTORY_EXTENDED

HOST_ID	ITEM_NAME	ITEM_VALUE
1	A	10
1	B	3
2	A	9
2	C	40

이렇게 바꾸는 방법

HOST_ID	A	B	C
1	10	3	0
2	9	0	40

1. SUM 으로 하는 방법

SELECT
    HOST_ID,
    SUM(A) AS A,
    SUM(B) AS B,
    SUM(C) AS C
FROM HISTORY_EXTENDED
GROUP BY HOST_ID

2. CASE WHEN ~ THEN 으로 하는 방법

SELECT
   HOST_ID,
   CASE WHEN ITEM_NAME = "A" THEN ITEM_VALUE END AS A,
   CASE WHEN ITEM_NAME = "B" THEN ITEM_VALUE END AS B,
   CASE WHEN ITEM_NAME = "C" THEN ITEM_VALUE END AS C
FROM HISTORY_EXTENDED

참고사이트

http://stackoverflow.com/questions/1241178/mysql-rows-to-columns