[MySQL] 행, 열 변환 작업

■ 행, 열 변환 작업

테이블의 행 데이터를 특정 기준으로 열 데이터로 변환하는 작업

---

■ 예시

과일 가게에서 판매하고 있는 과일은 [수박], [사과], [오렌지] 라고 특정할때, 날짜 별로 과일들의 수량을 계산하여 통계치를 구하려고 한다.

 

왼쪽은 과일이 판매될때 마다 작성한 데이터이고, 오른쪽은 구하고자 하는 결과 데이터이다. 

■ 작업

첫번째, 과일 가게 가계부 테이블를 생성한다.

CREATE TABLE `tbl_fruit_shop` (
  `pay_time` date NOT NULL COMMENT '구매 날짜',
  `fruit` varchar(50) NOT NULL COMMENT '과일',
  `cnt` int(11) NOT NULL COMMENT '수량'
)

반응형

---

두번째, 과일 가게 가계부 테이블를 조회했을 때 결과를 확인한다.

 * 결과 데이터는 [예시]의 왼쪽 데이터와 동일하다고 가정한다.

 

결과)

---

세번째, 각 행을 과일 별로 구분하여 데이터를 출력한다.

 * [CASE 문] 을 통해 행으로 구분되어 있던 데이터를 열로 수정

SELECT pay_time,
          CASE WHEN fruit='수박' THEN cnt END AS 수박,
          CASE WHEN fruit='사과' THEN cnt END AS 사과,
          CASE WHEN fruit='오렌지' THEN cnt END AS 오렌지
FROM tbl_fruit_shop
ORDER BY pay_time

반응형

결과)

---

네번째, 날짜 별로 과일들의 수량을 합산한다.

 * [SUM 함수] 와 [GROUP BY 문법]을 통해 데이터를 그룹화 함과 동시에 과일별 수량 합산

SELECT pay_time,
          SUM(CASE WHEN fruit='수박' THEN cnt END) AS 수박,
          SUM(CASE WHEN fruit='사과' THEN cnt END) AS 사과,
          SUM(CASE WHEN fruit='오렌지' THEN cnt END) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

결과)

---

다섯째, 날짜 별로 판매되지 않은 과일의 수량은 0으로 치환한다.

 * [COALESCE 함수]를 이용하여 NULL인 데이터는 0으로 치환

SELECT pay_time,
          COALESCE(SUM(CASE WHEN fruit='수박' THEN cnt END), 0) AS 수박,
          COALESCE(SUM(CASE WHEN fruit='사과' THEN cnt END), 0) AS 사과,
          COALESCE(SUM(CASE WHEN fruit='오렌지' THEN cnt END), 0) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

반응형

결과)

---