（4.28）for xml path 在合并拆分上的作用演示

for xml path 用于合并与拆分

 

1、合并

　　很多时候需要在SQL Server中创建逗号分隔列表。这可以使用SQL Server的DOR XML PATH功能完成。

　　与select语句一起使用时，FOR XML PATH生成一个xml。其示例如下所示。

 

【1.1】基本演示

DECLARE @tb TABLE(sno int identity,col1 varchar(10))
INSERT INTO @tb VALUES('a'),('b'),('c'),('d')
SELECT ',' + col1 from @tb
for xml path('')

 

　　上面的查询创建了一个表变量，并使用FOR XML PATH对其进行简单的选择。我们案例中的分隔符是“，”。上述查询的输出如下所示。

　　　　　　

　　我们得到一个逗号分隔列表，但是我们仍然需要摆脱“a”之前的第一个逗号。这可以使用STUFF功能完成，如下所示。

 

DECLARE @tb TABLE(sno int identity,
col1 varchar(10))
 
INSERT INTO @tb VALUES('a'),('b'),('c'),('d')
 
SELECT
STUFF(
(SELECT ',' + col1 from @tb
for xml path('')),
1,
1,
''
) AS Comma_separated_list

　　上面的查询删除了烦人的“，”并返回以下结果。

 

　　　　　　

【1.2】一个应用场景与FOR XML PATH应用

        首先呢！我们在增加一张学生表，列分别为（stuID,sName,hobby）,stuID代表学生编号，sName代表学生姓名，hobby列存学生的爱好！那么现在表结构如下：

           

        这时，我们的要求是查询学生表，显示所有学生的爱好的结果集，代码如下：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B 

         结果如下

 分析： 好的，那么我们来分析一下，首先看这句：

SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')

 

这句是通过FOR XML PATH 将某一姓名如张三的爱好，显示成格式为：“ 爱好1，爱好2，爱好3，”的格式！

那么接着看：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B  

　　剩下的代码首先是将表分组，在执行FOR XML PATH 格式化，这时当还没有执行最外层的SELECT时查询出的结构为:

　　　　

　　可以看到StuList列里面的数据都会多出一个逗号，这时随外层的语句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby  就是来去掉逗号，并赋予有意义的列明！

【1.3】最佳实践案例

SELECT STUFF((SELECT ','+字段名 FROM 表名 for xml path('')),1,1,'')

　　

 

 

2、拆分

【2.1】利用Xquery方式

　　这是将列表转换为表的快速SQL函数。

-- sql function to convert list to table
CREATE FUNCTION fn_listtotable
(
　　@list nvarchar(max),
　　@delimeter nvarchar(100)
)
RETURNS @split TABLE
(
　　sno int identity,
　　value varchar(max)
)
BEGIN
 
Declare @xml XML
select @xml = cast('<A>'+ replace(@list,@delimeter,
'</A><A>')+ '</A>' as xml)
 
INSERT INTO @split      
select t.value('.','varchar') as inVal
from @xml.nodes('/A') as x(t)
RETURN
END

上述查询利用SQL Server的Xquery功能来分割分隔值。该功能可以如下所示使用

　　　　

 

【2.2】利用split方式（推荐）

sql server2016自带函数：

SELECT value FROM STRING_SPLIT('A,B,C',',')  
/*结果：  
value  
-----  
A  
B  
C  

sql server2014及以下：

--split函数
--SELECT * FROM dbo.split('581::579::519::279::406::361::560',':')  
ALTER Function [dbo].[Split](@Sql varchar(8000),@Splits varchar(10))  
returns @temp Table (a varchar(100))  
As  
Begin  
　　Declare @i Int  
　　Set @Sql = RTrim(LTrim(@Sql))  
　　Set @i = CharIndex(@Splits,@Sql)  
　　While @i >= 1  
　　Begin 
 　　Insert @temp Values(Left(@Sql,@i-1)) 
　　 Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i) 
 　　Set @i = CharIndex(@Splits,@Sql) 
　　End 
　　If @Sql <> ''  
　　Insert @temp Values (@Sql)  
　　Return  
End 

案例演示

　　【1】原始数据

　　　　

　　【2】运行之后获得的数据

　　　　

 

【2.3】直接使用T-SQL列转行（数据量小可用）

列转行

 

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

--> 测试数据[huang]

if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] nvarchar(4),[b] nvarchar(10))
insert [huang]
select 'X1','1,4,8' union all
select 'X2','2' union all
select 'X3','3,6' union all
select 'X4','7' union all
select 'X5','5'

 

解决问题代码语句：

select 
    a.[a], 
    SUBSTRING(a.[b],number,CHARINDEX(',',[b]+',',b.number)-b.number) as [b] ,
    b.number
from
    [huang] a,master..spt_values b
where
    b.number >=1 and b.number<=len(a.[b])  
    and b.type='p' 
    and substring(','+a.[b],b.number,1)=','

--演示代码
select * from master..spt_values 
where type='p' 
order by number

----------------结果----------------------------