行列转换的一个解决思路，欢迎提意见

工作中常需要把数据库中某数据按照某列转成行。而展现的工具可能又只支持1句SQL完成，所以就考虑写视图解决。但是写此类SQL既繁琐，又容易出错，所以想写一个自动生成的脚本处理。
解决的问题，例：
表结构为：tab1
orgid(机构) gender(性别) ,itemid（项目） ，val（值）
0322 ,0,A,1
0322 ,0,B,4
0321 ,1,C,5
0324 ,1,C,3
展现效果：
0322,0,1,4,0
0321,0,0,0,5
0324,0,0,0,3
解决方法：
配置文件: SQL.txt
[code]
V_tab1.tab1.orgid,gender.VAL.itemid.A,B,C
[/code]
说明：
以"."分割列，分别是：视图名.表名,查询项.分组项.分组值(多值以","分割)

脚本: sql.sh

[code]
awk -F'.' '
{
test = $6
alen=split(test,cols,",");
for( idx=1;idx<=alen;idx++)
{
sqlalias[idx]="v" idx "_" $4
}

print " DROP VIEW " $1 " ; "
print " CREATE VIEW " $1 " \n AS "
print "SELECT " $3 ","

for( idx=1;idx<=alen;idx++)
{
if( idx == alen )
print " \t sum(v" idx "_" $4 ") AS v" idx "_" $4
else
print " \t sum(v" idx "_" $4 ") AS v" idx "_" $4 " ,"
}

print "FROM ("

for( idx=1;idx<=alen;idx++)
{
csql = "\tSELECT " $3 "," "\n \t \t "
if( idx != 1 ) csql = "\tUNION \n" csql
for( i=1;i<=alen;i++)
{
if( i == idx ) csql = csql $4
else csql = csql " 0 "
csql = csql " AS " sqlalias[i]
if( i != (alen) ) csql = csql " , "
}
csql = csql " \tFROM " $2 " WHERE " $5 "='\''" cols[idx] "'\''"
print csql
}
print " ) aaa "
print " GROUP BY " $3 ";"
}' SQL.txt > test.sql
[/code]

生成的代码:test.sql

[code]
DROP VIEW v_tab1 ;
CREATE VIEW v_tab1
AS
SELECT orgid,gender,
sum(v1_VAL) AS v1_VAL ,
sum(v2_VAL) AS v2_VAL ,
sum(v3_VAL) AS v3_VAL
FROM (
SELECT orgid,gender,
VAL AS v1_VAL , 0 AS v2_VAL , 0 AS v3_VAL FROM tab1 WHERE itemid='A'
UNION
SELECT orgid,gender,
0 AS v1_VAL , VAL AS v2_VAL , 0 AS v3_VAL FROM tab1 WHERE itemid='B'
UNION
SELECT orgid,gender,
0 AS v1_VAL , 0 AS v2_VAL , VAL AS v3_VAL FROM tab1 WHERE itemid='C'
) aaa
GROUP BY orgid,gender;
[/code]


使用方法：

[code]

select * from v_tab1
[/code]

适用范围：
报表，查询。
通过该方法，在没有报表工具的情况下解决了行列转换问题。
已知缺点：
1.由于不熟悉perl，简单解决方案，所以觉得用AWK处理，这样处理导致配置文件配置不灵活
2.在数据量过大的时候，这种查询效率也不会高，不过既然要行列转换，性能总是要有牺牲。
在db2中，没有oracle的decode类似的函数（也许有也可能，我没仔细去找），所以用union，不过我猜想即使用decode,效率也是差不多，当然仅仅是猜想而已，没在oralce下测试过。union实现要比左外连接效率要高很多，这方面测试过。
如果确实有更高性能要求，把创建试图语句改成创建表，每天晚上执行生成的insert语句即可。（BI项目的数据，通常至少延迟1天，所以可在晚上跑数据）


[color=blue]补充说明：
我是采用union实现的，但其实也可生成case when,decode(oralce)方式实现，但其实这与本主题并不矛盾。本方法不过是提出一个通过脚本生成相关SQL的思路，重点不在SQL技巧。
至于效率问题，如果很效率问题是瓶颈，可以改动AWK生成creat table ,insert语句，然后定时抽取。
我前面做的那个项目，就是通过类似手段解决问题的。简单描述：通过某种定制手段，直接生成SQL，实行数据仓库的清洗工作。这也是我们几个数据仓库的项目经验总结。经证实，这种方法确实切实有效，减少了大家相当多的工作量。前面一个数据仓库投入11个人左右，而这个项目我们只投入了7人，除项目经理外还有2人很少编码（做业务分析去了）。但是项目中，我们大量采用类似方法实现，带来了相当大效果。
后面这个项目数据量虽然比前面的小，但是业务其实更复杂。通过大量使用脚本，开发效率得到了大大提升。难怪以前的一个同事说：在他们现公司他们部门，所有的开发都是脚本，几乎不用脚本外的与语言。该兄弟其实可是C语言高手，现在完全投入到脚本怀抱中去了。[/color]