SQL查询案例：多行转换为一行

使用通常的方式
测试表与测试数据
CREATE TABLE TestTitle (

name   VARCHAR(10),

titleVARCHAR(10)

);



INSERT INTO TestTitle VALUES ('张三', '程序员');

INSERT INTO TestTitle VALUES ('张三', '系统管理员');

INSERT INTO TestTitle VALUES ('张三', '网络管理员');


INSERT INTO TestTitle VALUES ('李四', '项目经理');

INSERT INTO TestTitle VALUES ('李四', '系统分析员');


要求
对于测试数据，要求查询结果为：

张三程序员，系统管理员，网络管理员

李四项目经理，系统分析员

这种结构的结果。


思路
简单查看这个结果，很像对字符型的GROUP BY处理。

数值类型的可以SUM，但是字符类型的无法这么处理。

只好依次MAX(1) + MAX(2) + MAX(3)这种办法来处理。



实现
第一步，设置好分组的编号

SELECT

ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

name,

title

FROM

TestTitle

ORDER BY

name,

title


no                   name       title

-------------------- ---------- ----------

                   1李四        系统分析员

                   2李四        项目经理

                   1张三        程序员

                   2张三        网络管理员

                   3张三        系统管理员



第二步，根据有编号的子查询，进行分组处理

SELECT

name,

CASE WHEN COUNT(title) = 1 THEN MAX(title)

       WHEN COUNT(title) = 2 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + '，' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN titleELSE '' END )

       WHEN COUNT(title) = 3 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + '，' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN title + '，'ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 3 THEN titleELSE '' END )

END AS new_title

FROM

(

SELECT

    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

    name,

    title

FROM

    TestTitle

) subQuery

GROUP BY

name



执行结果

name       new_title

---------- ----------------------------------

李四        系统分析员，项目经理

张三        程序员，网络管理员，系统管理员




对于SQL Server 2005 以上版本使用FOR XML的方式
测试表与测试数据要求
与前面的一样


思路
首先把一个用户的数据,单独的读取出来

然后按照分组进行处理


实现
第一步 把一个用户的数据,单独的读取出来


SELECT

',' + title

FROM

TestTitle

WHERE

name = '张三'

FOR XML PATH('')




第二步Group By每个人


SELECT

name,

STUFF(

   (

   SELECT

     ',' + title

   FROM

     TestTitle subTitle

   WHERE

     name = TestTitle.name

   FOR XML PATH('')

   ),

   1, 1, '') AS allTitle

FROM

TestTitle

GROUP BY

name


执行结果

name      allTitle

---------- --------------------------------

李四        项目经理,系统分析员

张三        程序员,系统管理员,网络管理员



对于SQL Server 2005 以上版本使用 CTE 的处理方式 （使用递归方式处理）



WITH
t1  AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 AS
(
  SELECT
    t1.id,
    t1.name,
    CAST(t1.title AS varchar(100)) AS title
  FROM
    t1
  WHERE
    t1.id = 1
  UNION ALL
  SELECT
    t1.id,
    t2.name,
    CAST( t1.title + ',' + t2.title AS varchar(100)) AS title
  FROM
    t1, t2
  WHERE
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name,
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );



name       title

---------- -----------------------------------------------------------
-------------------------------
张三         系统管理员,网络管理员,程序员

李四         项目经理,系统分析员


(2 行受影响)





对于MySQL使用 GROUP_CONCAT 函数 的方式进行处理（非常简单）





mysql> SELECT
    ->   name,
    ->   GROUP_CONCAT(title) AS allTitle
    -> FROM
    ->   TestTitle
    -> GROUP BY
    ->   name;
+------+------------------------------+
| name | allTitle                     |
+------+------------------------------+
| 李四 | 项目经理,系统分析员          |
| 张三 | 程序员,系统管理员,网络管理员 |
+------+------------------------------+
2 rows in set (0.00 sec)





对于Oracle使用 WMSYS.WM_CONCAT 函数 的方式进行处理（也非常简单）



SQL>
SQL> SELECT
  2    name,
  3    WMSYS.WM_CONCAT(title) AS allTitle
  4  FROM
  5    TestTitle
  6  GROUP BY
  7    name;

NAME
----------
ALLTITLE
-------------------------------------------
李四
项目经理,系统分析员

张三
程序员,系统管理员,网络管理员



对于 DB2 ，也是使用 CTE 递归的方式处理


WITH
t1 (id, name, title) AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 (id, name, title) AS
(
  SELECT
    t1.id,
    t1.name,
    CAST(t1.title AS varchar(100)) AS title
  FROM
    t1
  WHERE
    t1.id = 1
  UNION ALL
  SELECT
    t1.id,
    t2.name,
    CAST( t1.title || ',' || t2.title AS varchar(100)) AS title
  FROM
    t1, t2
  WHERE
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name,
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );



NAME       TITLE

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

李四       项目经理,系统分析员

张三       网络管理员,系统管理员,程序员


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


http://hi.baidu.com/wangzhiqing999/blog/item/a57d1a86009e439ff703a67a.html