DB2行转列、列转行等操作

a616652325

DB2 行转列
　　----start
　　在网上看到这样一个问题：（问题地址：http://www.mydb2.cn/bbs/read.php?tid=1297&page=e&#a）






[c-sharp] view plaincopyprint?

• 班级  科目   分数
  
• 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语句创造虚拟字段，使结果集成为二维数组，然后应用聚合函数返回单一记录。怎么样？不理解，仔细看看下面的图和分析下面的语句你就理解了。

[c-sharp] view plaincopyprint?

• 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子句中的一个表产生了关系，取出了表中的一列作为数据.