行转列:
创建数据库表结构为:表名tmp
tabtype 为产品类型,构建 有产品表,分别对应A,B,C,D产品,这中结构好处,可以动态加入产品
目的:获取每天对应产品的金额
获取行转列sql:
select batchdate,
datetype,
sum(decode(tabtype, '1', amount, 0)) amount1,
sum(decode(tabtype, '3', amount, 0)) amount2,
sum(decode(tabtype, '5', amount, 0)) amount3,
sum(decode(tabtype, '7', amount, 0)) amount4,
sum(decode(tabtype, '9', amount, 0)) amount5,
sum(decode(tabtype, '11', amount, 0)) amount6,
sum(decode(tabtype, '2', amount, 0)) amount7,
sum(decode(tabtype, '4', amount, 0)) amount8,
sum(decode(tabtype, '6', amount, 0)) amount9,
sum(decode(tabtype, '8', amount, 0)) amount10,
sum(decode(tabtype, '10', amount, 0)) amount11
from tmp
where datetype = 'A'
group by batchdate, datetype
注释:decode(值,条件,是则值1,否则值2)
同理:学生,课程,成绩,也可以用这种结构行转列