sql行列转换例子（动态）

在CU上看到一个问题。 http://bbs.chinaunix.net/viewthread.php?tid=923817&extra=page%3D1
数据表中有下列记录：
   xm       sl
    王一    2
    王一    5
    张二    4
    张二    5
    张二    8
求SQL输出结果为：
   王一 2  王一 5
   张二 4  张二 5  张二 8

 

    类似的问题，其实经常可以在各BBS上看到有人提起，但从来没有人认为可以用一条SQL实现，一般回复者给出的解决方案都是用存储过程。不过既然自己搞DB2已近11个月，水平也应该逐步地提高了，不能始终停留在前人说不可能实现，自己就认为也不能实现的程度，有必要自己独立思索一下。

 

    这个问题的难点在于：需要分组字段对应的所有值的最大个数（行数）不明确。如果明确知道这个最大值的话是可以用CASE WHEN语句来实现的；在不知道这个最大值的情况下，首先让人想到的就是必须用循环来实现，但FOR,LOOP,WHILE,REPEAT这四种循环，在DB2中只有存储过程支持，这就不难理解为什么一般的解决方案都是说要用存储过程了。

 

    以前认真读过SQLLIB/samples下的很多例子，所以很自然地想到一个思路——递归。试了试，成功了，无论XM字段每种值对应多少行，都一样可用。实验过程如下：
DROP TABLE TEST;
CREATE TABLE TEST (XM  VARCHAR(8), SL INTEGER);
INSERT INTO TEST
       VALUES ('王一',  2),
              ('王一',  5),
              ('张二',  4),
              ('张二',  5),
              ('张二',  8),
              ('李三',  2),
              ('李三',  4),
              ('李三',  15),
              ('李三',  29);

WITH B (FATHER,SON,XM,CHAIN) AS
    (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100))
    FROM TEST A 
        UNION ALL
    SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || '  ' || C.SL
    FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B
    WHERE B.SON= C.FATHER)
SELECT
    D.XM  || '  ' || D.CHAIN
FROM
    (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
WHERE
    D.ROW_NUM = 1;

    效果如下：
db2 => WITH B (FATHER,SON,XM,CHAIN) AS
db2 (cont.) =>     (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100))
db2 (cont.) =>     FROM TEST A
db2 (cont.) =>         UNION ALL
db2 (cont.) =>     SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || '  ' || C.SL
db2 (cont.) =>     FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B
db2 (cont.) =>     WHERE B.SON= C.FATHER)
db2 (cont.) => SELECT
db2 (cont.) =>     D.XM  || '  ' || D.CHAIN
db2 (cont.) => FROM
db2 (cont.) =>     (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
db2 (cont.) => WHERE
db2 (cont.) =>     D.ROW_NUM = 1;
WITH B (FATHER,SON,XM,CHAIN) AS (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100)) FROM TEST A UNION ALL SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || '  ' || C.SL FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B WHERE B.SON= C.FATHER) SELECT D.XM  || '  ' || D.CHAIN FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D WHERE D.ROW_NUM = 1

1
--------------------------------------------------------------------------------------------------------------
SQL0347W  递归公共表表达式 "DB2ADMIN.B" 可能包含无限循环。  SQLSTATE=01605

李三  2          李三  4          李三  15         李三  29
王一  2          王一  5
张二  4          张二  5          张二  8

  已选择 3 条记录，打印 1 条警告消息。

db2 =>

 

    注：
    1、若想不看到SQL0347W这个警告，可以用db2 +w -t。
    2、ORACLE的解决方法在这里：http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php。它山之石，可以攻玉，有些思路也是可以借鉴的。

 

    最后我还是认为：这样的问题，用SQL来实现实在是自找麻烦，文字处理本来就是SHELL SCRIPT的强项，所以用awk会感觉异常简单。

11:31:10 ewdbkjy:[/home/ewadmin]$cat 9
王一               2
王一               5
张二               4
张二               5
张二               8
李三               2
李三               4
李三              15
李三              29
11:31:25 ewdbkjy:[/home/ewadmin]$awk '{a[$1]=a[$1]" "$1" "$2} END{for(i in a) print a[i]}' 9
 李三 2 李三 4 李三 15 李三 29
 张二 4 张二 5 张二 8
 王一 2 王一 5
11:31:45 ewdbkjy:[/home/ewadmin]$

========================================================================
任何形式的转载，请写明出处： 
email: [e-mail]
blog: http://blog.chinaunix.net/index.php?blogId=739   http://www.cublog.cn/u/739/
========================================================================

 

 

 

 

 

 

我的实践例子：

 

 

table:
CREATE TABLE TEST (XM  VARCHAR(8), SL INTEGER);
INSERT INTO TEST
       VALUES ('王一',  2),
              ('王一',  5),
              ('张二',  4),
              ('张二',  5),
              ('张二',  8),
              ('李三',  2),
              ('李三',  4),
              ('李三',  15),
              ('李三',  29);

 

case 1:

 

WITH B (FATHER,SON,XM,CHAIN) AS
    (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, 
  A.XM, CAST(CHAR(A.SL) AS VARCHAR(100))
     FROM TEST A 
    UNION ALL
     SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || '  ' || C.SL
     FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B
     WHERE B.SON= C.FATHER)

 

 SELECT  D.XM  || '  ' || D.CHAIN
 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
  WHERE  D.ROW_NUM = 1;

 

result:
李三  2          李三  4          李三  15         李三  29
王一  2          王一  5
张二  4          张二  5          张二  8

 

case 2:

 

WITH B (FATHER,SON,XM,CHAIN) AS
    (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON,
     A.XM, rtrim(ltrim(CHAR(A.SL))) 
    FROM TEST A 
    UNION ALL
    SELECT C.FATHER,C.SON,C.XM,rtrim(ltrim(B.CHAIN))||','||rtrim(ltrim(C.SL))
    FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,
    A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, 
         A.XM, rtrim(ltrim(CHAR(A.SL))) AS SL FROM TEST A) AS C, B
    WHERE B.SON= C.FATHER)

 

 SELECT  D.XM,D.CHAIN
 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
 WHERE D.ROW_NUM = 1;

 

result:
XM       CHAIN
-------- -----------
李三     2,4,15,29
王一     2,5
张二     4,5,8

 

case 3:

 

WITH B (FATHER,SON,XM,CHAIN) AS
    (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON,
     A.XM, rtrim(ltrim(CHAR(A.SL))) 
    FROM TEST A 
    UNION ALL
    SELECT C.FATHER,C.SON,C.XM,rtrim(ltrim(B.CHAIN))||rtrim(ltrim(C.SL))
    FROM (SELECT  A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,
    A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, 
         A.XM, rtrim(ltrim(CHAR(A.SL))) AS SL FROM TEST A) AS C, B
    WHERE B.SON= C.FATHER)
 -- end with

 

 SELECT  D.XM,D.CHAIN  
 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
 WHERE  D.ROW_NUM = 1;

 

result:
XM       CHAIN
-------- -----------
李三     241529
王一     25
张二     458