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

使用通常的方式

测试表与测试数据

CREATE TABLE TestTitle (

name   VARCHAR(10),

title VARCHAR(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 title ELSE '' 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 title ELSE '' 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

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

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

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

 

 

使用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

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

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

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