[PostgreSQL] SUM(ad_cnt) 결과가 여러 row 로 나오는데,이걸 1줄(One Row) + 여러 컬럼(PIVOT 형태) 로 만드는 방법

service_mid 별로 SUM(ad_cnt) 결과가 여러 row 로 나오는데,
이걸 1줄(One Row) + 여러 컬럼(PIVOT 형태) 로 만드는 방법에 대한 기록이다.

예시 형태:

👇 이것을

이렇게 가로 컬럼화(동적 pivot / 정적 pivot) 하는 방법.

1) service_mid 값이 정해져 있는 경우 (정적 PIVOT)

가장 빠르고 안정적.

SELECT
    t1.id,

    SUM(CASE WHEN t2.service_mid = 'A' THEN t2.ad_cnt ELSE 0 END) AS a_cnt,
    SUM(CASE WHEN t2.service_mid = 'B' THEN t2.ad_cnt ELSE 0 END) AS b_cnt,
    SUM(CASE WHEN t2.service_mid = 'C' THEN t2.ad_cnt ELSE 0 END) AS c_cnt

FROM tb1 t1
LEFT JOIN tb2 t2 ON t2.tb1_id = t1.id
GROUP BY t1.id;

2) tb1 한 건 기준으로 1줄 Pivot (t1.id 조건 포함)

SELECT
    SUM(CASE WHEN service_mid = 'A' THEN ad_cnt ELSE 0 END) AS a_cnt,
    SUM(CASE WHEN service_mid = 'B' THEN ad_cnt ELSE 0 END) AS b_cnt,
    SUM(CASE WHEN service_mid = 'C' THEN ad_cnt ELSE 0 END) AS c_cnt
FROM tb2
WHERE tb1_id = t1.id;

3) service_mid 값이 동적으로 계속 늘어난다면? (JSON 기반 1줄 컬럼화)

PostgreSQL은 동적 PIVOT이 없어 → 보통 JSON으로 한 줄 처리.

SELECT 
    t1.id,
    JSON_OBJECT_AGG(service_mid, sum_cnt) AS mid_sum_map
FROM (
    SELECT service_mid, SUM(ad_cnt) AS sum_cnt, tb1_id
    FROM tb2
    GROUP BY tb1_id, service_mid
) x
JOIN tb1 t1 ON x.tb1_id = t1.id
GROUP BY t1.id;

결과 예:

{
  "A": 10,
  "B": 20,
  "C": 5
}

→ 자바에서 Map<String, Integer> 로 바로 받을 수 있음.

사용 목적에 따라 추천 방식