FOR XML PATH及行列变换，SUBSTRING_INDEX行转换

SQL Server中有提供一个FOR XML PATH的子句（须知sql2005及以上版本才能用），用来将查询结果行输出成XML格式，我们可以通过这个语法做一些变通实现一些特定的功能，比如说行转列。

环境

SQL Server数据库dog表

FOR XML PATH基础格式

FOR XML PATH子句会将查询结果行输出为XML格式

使用

SELECT [no], name  
FROM dog FOR XML PATH;

结果

<row>
	<no>15701</no>
	<name>校花</name>
</row>
<row>
	<no>15703</no>
	<name>小邋遢</name>
</row>
<row>
	<no>15702</no>
	<name>校草</name>
</row>
<row>
	<no>15704</no>
	<name>三毛</name>
</row>

限制条件
在FOR XML PATH子句前添加WHERE子句限定条件范围来限定获取信息的范围（也可以使用自连接限定范围）。

FOR XML PATH的行列变化

行标签 PATH字符串参数

FOR XML PATH子句可支持传递参数的，即FOR XML PATH(schema)，在查询的时候就会将行标签<row>替换为<schema>，要注意的是schema必须是字符串。

SELECT [no], name  
FROM dog FOR XML PATH('dog');

结果

<dog>
	<no>15701</no>
	<name>校花</name>
</dog>
<dog>
	<no>15703</no>
	<name>小邋遢</name>
</dog>
<dog>
	<no>15702</no>
	<name>校草</name>
</dog>
<dog>
	<no>15704</no>
	<name>三毛</name>
</dog>

去列标签

利用XML的特点，即空标签不会存在的特定，将行标签<row>去掉。

SELECT [no], name  
FROM dog FOR XML PATH('');

结果

<no>15701</no>
<name>校花</name>
<no>15703</no>
<name>小邋遢</name>
<no>15702</no>
<name>校草</name>
<no>15704</no>
<name>三毛</name>

列标签 别名

给列起别名的方式来更改或去掉除列标签

SELECT [no] AS lable, name AS nickname 
FROM dog FOR XML PATH('');

结果

<lable>15701</lable>
<nickname>校花</nickname>
<lable>15703</lable>
<nickname>小邋遢</nickname>
<lable>15702</lable>
<nickname>校草</nickname>
<lable>15704</lable>
<nickname>三毛</nickname>

注：列标签不能将别名定义为空字符串，否则会报如下错误

> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请将别名更改为有效名称。 (1038)

去列标签

方法：不起别名，且给字段加上空字符串（加空字符串为了保持原值不变，如有需要亦可加其他字符串）

SELECT [no] + '', name + '' 
FROM dog FOR XML PATH('');

结果

15701校花15703小邋遢15702校草15704三毛

实现行转列

根据上述的FOR XML PATH的行列变化，我们可以来实现行转列。

SELECT STUFF((SELECT ';' + name FROM dog FOR XML PATH ('')), 1, 1, '') AS name;

结果

校花;小邋遢;校草;三毛

关于行列转换函数：SQL SERVER还提供了另外的行转列的PRIVOT函数和列转行的UNPRIVOT函数。

替换

STUFF函数（推荐）

STUFF(character_expression, start, length, character_expression)

或REPLACE函数去除分号mysql

REPLACE(string_expression, string_pattern, string_replacement)

判断包含

INSTR(str,substr)
FIND_IN_SET(str,strlist)

GROUP_CONCAT

在MYSQL中行转列使用GROUP_CONCAT，多行变一行

-- 分割符默认逗号
GROUP_CONCAT([DISTINCT] 列名 [ORDER BY 列名 DESC/ASC] [SEPARATOR 字符串])

SUBSTRING_INDEX

SUBSTRING_INDEX(str, delim, count)

参数	含义
str	需要拆分的字符串
delim	分隔符，通过某字符进行拆分
count	当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

select SUBSTRING_INDEX(SUBSTRING_INDEX('1,2',',',help_topic_id+1),',',-1) AS num
FROM mysql.help_topic  
where help_topic_id < LENGTH('1,2')-LENGTH(REPLACE('1,2',',',''))+1

mysql 库的 help_topic 表的 help_topic_id 来作为变量，因为help_topic_id 是从0自增的,最大长度642，当然也可以用其他表的自增字段(从1开始自增，where跟select使用自增字段时需要特别注意)辅助。