在数据库报表查询时事实表中的数据很多情况下没有直接反映最终报表需求的格式,其中很典型的就是要把多行的数据转换成列的形式显示,举例如下:
事实表T_Score(学生成绩表):
CREATE TABLE "DB2ADMIN"."T_SCORE" (
"NAME" VARCHAR(20) ,
"SUBJECT" VARCHAR(20) ,
"SCORE" INTEGER ) ;
示例数据:
Mary Chinese 90
Mary English 84
Mary Maths 79
Tom Chinese 86
Tom English 93
Tom Maths 89
但是报表需求:
NAME Chinese English Maths
Mary 90 84 79
Tom 86 93 89
SQL实现:
select name,
sum(decode(subject,'Chinese',score)) as Chinese,
sum(decode(subject,'English',score)) as English,
sum(decode(subject,'Maths',score)) as Maths
from t_score group by name;
另外还有一种需求是希望把多行的数据合并到一列中,可以利用oracle的树结构查询获得(connect by 语句):
select sys_connect_by_path(r_name,'|'),level LV from
(select r_name,row_number() over (order by id_stock desc) rn
from t_stocks_fact) where level>3 connect by rn=prior rn+1;
至于列转为行则可以用union all来实现,以T_Score_Row为例,
select name, 'Chinese' as subject, chinese from T_Score_Row
union all
select name, 'English' as subject, english from T_Score_Row
union all
select name, 'Maths' as subject, maths from T_Score_Row
以下SQL是上面行转列过程的逆操作而已。
select * from (
select name, 'Chinese' as subject, chinese from (
select name,
sum(decode(subject,'Chinese',score)) as Chinese,
sum(decode(subject,'English',score)) as English,
sum(decode(subject,'Maths',score)) as Maths
from t_score group by name)
union all
select name, 'English' as subject, english from (
select name,
sum(decode(subject,'Chinese',score)) as Chinese,
sum(decode(subject,'English',score)) as English,
sum(decode(subject,'Maths',score)) as Maths
from t_score group by name
)
union all
select name, 'Maths' as subject, maths from (
select name,
sum(decode(subject,'Chinese',score)) as Chinese,
sum(decode(subject,'English',score)) as English,
sum(decode(subject,'Maths',score)) as Maths
from t_score group by name
)
) order by name