行转列
SELECT NAME,
sum(DECODE(Quarter,1,count,0)) AS '一季度',
sum(DECODE(Quarter,2,count,0)) AS '二季度',
sum(DECODE(Quarter,3,count,0)) AS '三季度'
from Sale
group by name
WITH SALE AS
(SELECT 1 ID, 'Ran' NAME, 1 QUARTER, 10 COUNT
FROM DUAL
UNION ALL
SELECT 2, 'Ran', 2, 11
FROM DUAL
UNION ALL
SELECT 3, 'Ran', 3, 12
FROM DUAL
UNION ALL
SELECT 4, 'Juan', 1, 16
FROM DUAL
UNION ALL
SELECT 5, 'Juan', 2, 18
FROM DUAL
UNION ALL
SELECT 6, 'Juan', 3, 16
FROM DUAL
UNION ALL
SELECT 7, 'Ping', 1, 11
FROM DUAL
UNION ALL
SELECT 8, 'Ping', 2, 11
FROM DUAL
UNION ALL
SELECT 9, 'Ping', 3, 12 FROM DUAL)
SELECT NAME,
sum(CASE QUARTER
WHEN 1 THEN
COUNT
END) 一季度,
sum(CASE QUARTER
WHEN 2 THEN
COUNT
END) 二季度,
sum(CASE QUARTER
WHEN 3 THEN
COUNT
END) 三季度
FROM SALE
GROUP BY NAME
--你这个数量的字段 count 最好不要使用关键字,case when 与decode
select name,sum(decode(Quarter),1,count)) 一季度,
sum(decode(Quarter),2,count)) 二季度,
sum(decode(Quarter),3,count)) 三季度,
sum(decode(Quarter),4,count)) 四季度
from Sale
group by name
select name,sum(case when Quarter=1 then count end) 一季度,
sum(case when Quarter=2 then count end) 二季度,
sum(case when Quarter=3 then count end) 三季度,
sum(case when Quarter=4 then count end) 四季度
from sale
group by name
