关于Oracle的行转列,示例如下:
源表数据:
select 1 id,'A' name,'1' jidu,1100 xiaoshou from dual
union
select 1 id,'A' name,'2' jidu,1200 xiaoshou from dual
union
select 1 id,'A' name,'3' jidu,1300 xiaoshou from dual
union
select 1 id,'A' name,'4' jidu,1400 xiaoshou from dual
union
select 2 id,'B' name,'1' jidu,2100 xiaoshou from dual
union
select 2 id,'B' name,'2' jidu,2200 xiaoshou from dual
union
select 2 id,'B' name,'3' jidu,2300 xiaoshou from dual
union
select 2 id,'B' name,'4' jidu,2400 xiaoshou from dual
union
select 3 id,'C' name,'1' jidu,3100 xiaoshou from dual
union
select 3 id,'C' name,'2' jidu,3200 xiaoshou from dual
union
select 3 id,'C' name,'3' jidu,3300 xiaoshou from dual
union
select 3 id,'C' name,'4' jidu,3400 xiaoshou from dual
union
select 4 id,'D' name,'1' jidu,4100 xiaoshou from dual
union
select 4 id,'D' name,'2' jidu,4200 xiaoshou from dual
union
select 4 id,'D' name,'3' jidu,4300 xiaoshou from dual
union
select 4 id,'D' name,'4' jidu,4400 xiaoshou from dual
根据ID和NAME进行分组后,使用SUM和DECODE函数对JIDU进行处理,然后赋值XIAOSHOU,如下:
SELECT T.ID,
T.NAME,
sum(decode(t.jidu,'1',t.xiaoshou)) as jd1,
sum(decode(t.jidu,'2',t.xiaoshou)) as jd2,
sum(decode(t.jidu,'3',t.xiaoshou)) as jd3,
sum(decode(t.jidu,'4',t.xiaoshou)) as jd4
FROM (
select 1 id,'A' name,'1' jidu,1100 xiaoshou from dual
union
select 1 id,'A' name,'2' jidu,1200 xiaoshou from dual
union
select 1 id,'A' name,'3' jidu,1300 xiaoshou from dual
union
select 1 id,'A' name,'4' jidu,1400 xiaoshou from dual
union
select 2 id,'B' name,'1' jidu,2100 xiaoshou from dual
union
select 2 id,'B' name,'2' jidu,2200 xiaoshou from dual
union
select 2 id,'B' name,'3' jidu,2300 xiaoshou from dual
union
select 2 id,'B' name,'4' jidu,2400 xiaoshou from dual
union
select 3 id,'C' name,'1' jidu,3100 xiaoshou from dual
union
select 3 id,'C' name,'2' jidu,3200 xiaoshou from dual
union
select 3 id,'C' name,'3' jidu,3300 xiaoshou from dual
union
select 3 id,'C' name,'4' jidu,3400 xiaoshou from dual
union
select 4 id,'D' name,'1' jidu,4100 xiaoshou from dual
union
select 4 id,'D' name,'2' jidu,4200 xiaoshou from dual
union
select 4 id,'D' name,'3' jidu,4300 xiaoshou from dual
union
select 4 id,'D' name,'4' jidu,4400 xiaoshou from dual) T
GROUP BY T.ID,T.NAME
order by t.id,t.name
结果如图: