데이터베이스 질문과 답변
SELECT 'a001' as code,
50 as hap,
1 as gubun,
'apple' as text1,
'banana' as text2
UNION ALL
SELECT 'a001' as code,
60 as hap,
1 as gubun,
'apple' as text1,
'banana' as text2
UNION ALL
SELECT 'a001' as code,
70 as hap,
1 as gubun,
'apple' as text1,
'banana' as text2
UNION ALL
SELECT 'a001' as code,
80 as hap,
1 as gubun,
'apple' as text1,
'banana' as text2
UNION ALL
SELECT 'a001' as code,
90 as hap,
2 as gubun,
'apple' as text1,
'banana' as text2
UNION ALL
SELECT 'a001' as code,
20 as hap,
2 as gubun,
'apple' as text1,
'banana' as text2
안녕하세요 mssql 로 원하는 데이터를 뽑고싶은데, 잘되지 않아서 여쭤봅니다. 많은 도움 부탁드립니다.
예를 들어서 테이블을 select해서 이러한 데이터 들이 나왔을때, code로 group by를 해서 똑같은 컬럼들을 보여줄수있는 방법은 없나요?
또, 그룹 된 애들을 gubun 으로 1인 사람들끼리의 hap를 sum 하고 2인사람끼리 hap 을 하고 싶습니다.
SELECT 'a001' as code,
'apple' as text1,
'banana' as text2,
260 as sum1,
110 as sum2
식으로 보여주고싶습니다.
현재 예제로 만든것에는 code가 a001뿐이지만 여러개있습니다.
by 마농
[2017.11.23 20:40:47]
WITH t AS
(
SELECT 'a001' code, 50 hap, 1 gubun, 'apple' text1, 'banana' text2
UNION ALL SELECT 'a001', 60, 1, 'apple', 'banana'
UNION ALL SELECT 'a001', 70, 1, 'apple', 'banana'
UNION ALL SELECT 'a001', 80, 1, 'apple', 'banana'
UNION ALL SELECT 'a001', 90, 2, 'apple', 'banana'
UNION ALL SELECT 'a001', 20, 2, 'apple', 'banana'
)
-- 1. GROUP BY & SUM(CASE)
SELECT code
, text1
, text2
, SUM(CASE gubun WHEN 1 THEN hap END) sum1
, SUM(CASE gubun WHEN 2 THEN hap END) sum2
FROM t
GROUP BY code, text1, text2
;
-- 2. PIVOT
SELECT *
FROM t
PIVOT (SUM(hap) FOR gubun IN ("1", "2")) a
;
) 버튼을 클릭하여 작성 하시면 됩니다.