DB2行转列、列转行等操作

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