将字符串转换成表

有时会遇到，字符串中存在分隔符，需要将其转换成表，和其他table进行join查询；有时会遇到相反的情况，需要将表中的数据行拼接成一个字符串显示。

一，将字符串转换成表

使用XML来进行转换，性能快，代码简洁，

1，示例，字符串的分隔符是逗号，将其转换成xml。

    declare @separator varchar(10)
    declare @str varchar(max)
    
    set @separator=','
    set @str='54,57,55,56,59'

    declare @xml xml
    set @xml=convert(xml,'<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')

    SELECT ids=N.v.value('.', 'int') FROM @xml.nodes('/v') N(v)

2，示例，字符串的分隔符是逗号，将其转换成xml。

    declare @separator varchar(10)
    declare @str varchar(max)
    
    set @separator=','
    set @str='54,57,55,56,59'

    declare @xml xml
    set @xml=convert(xml,'<Item v=''' + REPLACE(@str, @separator, '''></Item><Item v=''') + '''></Item>')
    
    SELECT ids=N.v.value('@v', 'int') 
    FROM @xml.nodes('/Item') N(v)

3，创建表值函数

CREATE FUNCTION dbo.fn_split
(
    @str varchar(max),
    @separator varchar(max)
)
RETURNS @dt TABLE 
(
    value int
)
AS
BEGIN
    declare @xml xml
    set @xml=convert(xml,'<Item v=''' + REPLACE(@str, @separator, '''></Item><Item v=''') + '''></Item>')
    
    insert into @dt
    SELECT N.v.value('@v', 'int') 
    FROM @xml.nodes('/Item') N(v)
    
    RETURN 
END

4，内置表值函数

SQL Server 2016 新增一个表值函数string_split，用于按照分隔符将字符串分割成表值数据，返回的字段名是Value

STRING_SPLIT ( string , separator )  

二，将表数据拼接成字符串

create table dbo.test
(
id int,
txt nvarchar(10)
)

select id,(select a.txt+'' from dbo.test a where a.id=t.id for xml path('')) as descr
from dbo.test t 
group by id