行转列
select * from (select name, nums from ( SELECT 1 id, '苹果' name, 1000
nums FROM DUAL UNION ALL
SELECT 2 id, '苹果' name, 2000
nums FROM DUAL UNION ALL
SELECT 3 id, '苹果' name, 4000
nums FROM DUAL UNION ALL
SELECT 4 id, '橘子' name, 5000
nums FROM DUAL UNION ALL
SELECT 5 id, '橘子' name, 3000
nums FROM DUAL UNION ALL
SELECT 6 id, '葡萄' name, 3500
nums FROM DUAL UNION ALL
SELECT 7 id, '芒果' name, 4200
nums FROM DUAL UNION ALL
SELECT 8 id, '芒果' name, 5500
nums FROM DUAL)) pivot (sum(nums) for name in ('苹果' 苹果, '橘子' 橘子, '葡萄' 葡萄, '芒果' 芒果));
或
SELECT T.A,LISTAGG(B,',') WITHIN GROUP (ORDER BY B DESC) FROM (
SELECT 1 A ,1 B FROM DUAL UNION ALL
SELECT 1 A ,2 B FROM DUAL UNION ALL
SELECT 2 A ,3 B FROM DUAL UNION ALL
SELECT 3 A ,4 B FROM DUAL UNION ALL
SELECT 3 A ,5 B FROM DUAL ) T
GROUP BY T.A ;
列传行
select id , name, jidu, xiaoshou from ( SELECT 1 id,'苹果' name,1000 Q1,2000 Q2,3300 Q3,5000 Q4
FROM DUAL UNION ALL
SELECT 2 id,'橘子' name,3000 Q1,3000 Q2,3200 Q3,1500 Q4
FROM DUAL UNION ALL
SELECT 3 id,'香蕉' name,2500 Q1,3500 Q2,2200 Q3,2500 Q4
FROM DUAL UNION ALL
SELECT 4 id,'葡萄' name,1500 Q1,2500 Q2,1200 Q3,3500 Q4
FROM DUAL) unpivot (xiaoshou for jidu in (q1, q2, q3, q4) ) ;