ORACLE 行转列求大神帮助

语句：select BANK_ACC , substr(TXDATE,1,6),SUM(AMT) FROM FUND_DETAIL_HIS GROUP BY BANK_ACC，TXDATE ORDER BY substr(TXDATE,1,6) ,BANK_ACC

得到以上结果，现在我想把结果格式做成

求大神指点一下，上面的代码该怎么改呢，谢谢！

使用union 转换，就好了

给你个参考：

SELECT name,SUM(CASE WHEN type='好' THEN count END) hao,
SUM(CASE WHEN type='差' THEN count END) cha
FROM (SELECT name,COUNT(type) count, type FROM a GROUP BY name, type)
GROUP BY name

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual
)

select nation,
max(decode(ranking, '第一', city, '')) as 第一,
max(decode(ranking, '第二', city, '')) as 第二,
max(decode(ranking, '第三', city, '')) as 第三,
max(decode(ranking, '第四', city, '')) as 第四
from temp group by nation;

wmsys.wm_concat函数，很简单的，可以参考https://blog.csdn.net/gameloft9/article/details/42805137

--考虑到不止一年，所以多加了个年月，3-12月你直接参考写就行
--原理decode判断月份是否为一月或二月，是取amt值,不是取0
select BANK_ACC as 银行账号,
substr(TXDATE,1,4) as 年月,
SUM(decode(substr(TXDATE,5,2),'01',AMT,0)) as 一月,
SUM(decode(substr(TXDATE,5,2),'02',AMT,0)) as 二月
FROM FUND_DETAIL_HIS
GROUP BY BANK_ACC，substr(TXDATE,1,4)
ORDER BY substr(TXDATE,1,4)，BANK_ACC

----**此需求将12个月份作为12个字段，截取日期月份，当为'01'时取AMT值，否则取0，sum起来即为1月份的值，其他月份类推**----
select BANK_ACC ,
substr(TXDATE,1,4) as 年份,
sum(decode(substr(TXDATE,-2),'01',AMT,0)) as 一月,
sum(decode(substr(TXDATE,-2),'02',AMT,0)) as 二月,
sum(decode(substr(TXDATE,-2),'03',AMT,0)) as 三月,
sum(decode(substr(TXDATE,-2),'04',AMT,0)) as 四月,
sum(decode(substr(TXDATE,-2),'05',AMT,0)) as 五月,
sum(decode(substr(TXDATE,-2),'06',AMT,0)) as 六月,
sum(decode(substr(TXDATE,-2),'07',AMT,0)) as 七月,
sum(decode(substr(TXDATE,-2),'08',AMT,0)) as 八月,
sum(decode(substr(TXDATE,-2),'09',AMT,0)) as 九月,
sum(decode(substr(TXDATE,-2),'10',AMT,0)) as 十月,
sum(decode(substr(TXDATE,-2),'11',AMT,0)) as 十一月,
sum(decode(substr(TXDATE,-2),'12',AMT,0)) as 十二月
FROM FUND_DETAIL_HIS
GROUP BY BANK_ACC,substr(TXDATE,1,4)
ORDER BY BANK_ACC,substr(TXDATE,1,4)

给你一个行转列模板

select * from (select 月份，银行账号，钱 from XXX)
pivot( sum(钱)
for 月份字段名 in （‘一月’一月，.......）
)