一行变多行

http://msdn.microsoft.com/zh-cn/library/ms188282.aspx这个网址里介绍了nodes、value函数，大家可以从这里详细的学习。下面说说我的心得。

最近遇到个问题，一个字段中含有分号，要按照分号分成多行，从网上查了一下，现把我这两天学习的总结一下，希望可以帮助大家。

表格PkTable如下：

id	txt
1	aa;bb

 

想转化成如下进行统计：

id	txt
1	aa
1	bb

 

 

查询方法如下：

select

a.id,b.txt

from

(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a

outer apply

(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b

我解释下这个sql中用到的一些语法：

（1）replace函数，是将';'替换为了'</v><v>'，又经'+'连接，经convert类型转化，表a的数据如下：

id	txt
1	<root><v>aa</v><v>bb</v></root>

 

(2)在b表中，a.txt指的是a表的txt列，nodes函数的作用是根据参数中的字符串（标签）将xml转化为关系数据集，即转化为行 ，C(v)是别名，C为表明，v为列名。

通过一下语句进行查询C表的数据：

select C.v.query('.') txt
  from PkTable
 outer apply txt.nodes('/root/v')as C(v)

查询结果如下：

txt

<v>aa</v>

<v>bb</v>

 

 

(3)C.v.value('.','nvarchar(100)')，C是表，v是列，value函数是读取标签之间的值，对于这个列子，读取的为<v>和</v>中间的值；

value的第一个参数是一个字符串文字，从 XML 实例内部检索数据。 XQuery 必须最多返回一个值。 否则，将返回错误；

value的第二个参数是指将查询结果转化为何种类型的数据。

此处，'.'表示当前目录，即<v>目录，另外'..'表示上级目录，'/'表示根目录，这个跟Linux是一样的。

通过以下语句查询b表：

select b.txt

from

(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a

outer apply

(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b

b表的数据如下：

txt

aa

bb

 

 

（4）最后就是outer apply了，他是先定好a表的列，然后取b表的列插入。对于这个函数，大家可以从网上查询，我也不是太了解。

最后的查询结果为：

id	txt
1	aa
1	bb