DB2 行转列
----start
在网上看到这样一个问题：（问题地址：http://www.mydb2.cn/bbs/read.php?tid=1297&page=e&#a）
- 班级 科目 分数
- 1 语文 8800
- 1 数学 8420
- 1 英语 7812
- ……
- 2 语文 8715
- 2 数学 8511
- 2 英语 8512
- ……
- 要求转换成下面这样的结果
- 班级 语文 数学 英语
- 1 8800 8420 7812
- 2 8715 8511 8512
这是一个非常经典的 4属性的表设计模式，顾名思义，这样的表一般有四列，分别是：entity_id, attribute_name,attribute_type, attribute_value ，这样的设计使我们添加字段非常容易，如：我们想添加一个物理成绩是非常简单的，我们只要向表中插入一条记录即可。但是，这样的设计有一个非常严重的问题，那就是：查询难度增加，查询效率非常差。
要想实现上面的查询有一个原则，那就是：通过case语句创造虚拟字段，使结果集成为二维数组，然后应用聚合函数返回单一记录。怎么样？不理解，仔细看看下面的图和分析下面的语句你就理解了。
- create table score
- (
- banji integer,
- kemu varchar(10),
- fengshu integer
- )
- go
- insert into score values
- (1, '语文', 8800),
- (1, '数学', 8420),
- (1, '英语', 7812),
- (2, '语文', 8715),
- (2, '数学', 8511),
- (2, '英语', 8512)
- go
- select banji,
- max(yuwen) 语文,
- max(shuxue) 数学,
- max(yingyu) 英语
- from
- (select banji,
- case kemu
- when '语文' then fengshu
- else 0
- end yuwen,
- case kemu
- when '数学' then fengshu
- else 0
- end shuxue,
- case kemu
- when '英语' then fengshu
- else 0
- end yingyu
- from score
- ) as inner
- group by inner.banji
- order by 1
- go
你可能正在感叹，这样的解决方案是多么的巧妙，可惜不是我想出来的，在这里，我也不敢把大师的思想据为己有，以上思想来自<SQL语言艺术>的第11章，想了解更全面的信息，大家可以参考。
---更多参见：DB2 SQL 精萃
----声明：转载请注明出处。
----last updated on 2009.12.20
----written by ShangBo on 2009.12.16
----end
DB2 列转行
行转列
给出下面的数据:
CREATE TABLE Sales (Year INT, Quarter INT, Results INT)
YEAR QUARTER RESULTS
----------- ----------- -----------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27
想要的到结果:
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30 15 10
2005 18 40 12 27
这个SQL就可解决这个问题:
SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,
MAX(CASE WHEN Quarter = 2
THEN Results END) AS Q2,
MAX(CASE WHEN Quarter = 3
THEN Results END) AS Q3,
MAX(CASE WHEN Quarter = 4
THEN Results END) AS Q4
FROM Sales
GROUP BY Year
解释一下为什么要加max的原因，因为不加max的话结果会是这样：
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 - - -
2004 - 30 - -
2004 - - 15 -
2004 - - - 10
2005 18 - - -
2005 - 40 - -
2005 - - 12 -
2005 - - - 27
列转行
给出下面数据
CREATE TABLE SalesAgg
( year INTEGER,
q1 INTEGER,
q2 INTEGER,
q3 INTEGER,
q4 INTEGER );
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30 15 10
2005 18 40 12 27
想要的结果
YEAR QUARTER RESULTS
----------- ----------- -----------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27
这个SQL就可以实现：
SELECT S.Year, Q.Quarter, Q.Results
FROM SalesAgg AS S,
TABLE (VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(Quarter, Results);
每个values中对应列的数据类型必须相同，值可以任意，如1，2，3，4都是整形
下面解释一下执行的过程：
核心是用table函数创建了一个表，这个表是用value实现的多行表，value实现虚表的例子：
db2 => select * from (values (1,2),(2,3)) as t1(col1,col2)
COL1 COL2
----------- -----------
1 2
2 3
2 条记录已选择。
db2 => select * from (values 1) as a
1
-----------
1
1 条记录已选择。
所不同的是这里跟from子句中的一个表产生了关系，取出了表中的一列作为数据.