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
-------------------------------------------
李四
项目经理,系统分析员
张三
程序员,系统管理员,网络管理员
对于 PostgreSQL 使用 string_agg 函数 的方式进行处理（也非常简单）
Test=#
Test=# SELECT
Test-# name,
Test-# string_agg(title,',') AS allTitle
Test-# FROM
Test-# TestTitle
Test-# GROUP BY
Test-# name;
name | alltitle
------+------------------------------
李四 | 项目经理,系统分析员
张三 | 程序员,系统管理员,网络管理员
(2 行记录)
对于 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 条警告消息。
-------------------------------------------------------------------------------------------------------------------------------------------------------
FOR XML PATH 的作用是把结果以xml文本的形式显示出来，也就是说，最终结果就是一个字符串，因此我们就不需要使用什么字符串合并函数了。
STUFF函数的原型是 Stuff(str1, start, len, str2)，作用是，删掉str1中start开始的len个字符，用str2替换。因此，可以起到在多个项之间插入分隔符。
比如，
select ','+name from student for xml path('') group by class |
输出结果可能是
,Jim,Kate,Tom,Sally
如果使用STUFF，可以删掉第一个空格
stuff(select ','+name from student for xml path('') group by class, 1, 1, '') |
- if(value != null && value instanceof org.hibernate.lob.SerializableClob){
- org.hibernate.lob.SerializableClob clob = (org.hibernate.lob.SerializableClob)value;
- try {
- Reader reader = clob.getCharacterStream();
- BufferedReader br = new BufferedReader(reader);
- StringBuilder sb = new StringBuilder();
- String str = null;
- while((str = br.readLine()) != null){
- sb.append(str);
- }
- _map.put(key.toUpperCase(), sb.toString());
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
