目录
概述
STRING_AGG(合并):多行数据合并成一个字符串,以逗号隔开。
STRING_SPLIT(拆分):一个字符串,拆分成多行。
一、多行数据合并成一个字符串
1、通过 FOR xml path('') 合并字符串记录
根据name字段,合并code
declare @table1 table ( id int ,code varchar(10) , name varchar(20) );insert into @table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2, 'm2',null ), ( 3, 'm3', 'c' ), ( 4, 'm2','d' ), ( 5, 'm1','c');select * from @table1;select name, files=stuff((select ','+convert(varchar, code)from @table1bwhere a.name=b.namefor xml path('')), 1, 1, '')from @table1agroup by name;
结果:
2、MS SQL Server的2017新增了STRING_AGG()是一个聚合函数
它将由指定的分隔符分隔将字符串行连接成一个字符串。 它不会在结果字符串的末尾添加分隔符。
SELECT name, string_agg(code,';') files FROM @table1 GROUP BY name;
二、一个字符串拆分成多行
1、拆一列数据:
将如下从Excel复制的一栏数据,插入到表中行进显示(同时去掉回车换行符,空白和Tab符号):
1、利用XML解析方式(推荐)
declare @moulds varchar(4000);set @moulds='55-480730-03,
55-487780-01,
,
55-487780-02';declare @table1 table(col1 nvarchar(4000));declare @table2 table(col1 nvarchar(40),xmlval1 xml);insert into @table1 values(replace(@moulds, char(13)+char(10), ''));select * from @table1
insert into @table2
select rtrim(ltrim(replace(bs.v1, char(9), ''))),a.xmlval1from (select convert(xml, ''+replace(replace(col1, ',', ','), ',', '')+'') as xmlval1
from @table1) across apply(select k.n.value('.', 'nvarchar(80)') v1 from a.xmlval1.nodes('n') k(n) ) bswhere bs.v1 !='';select * from @table2;
结果:
2、利用字符串拆解
declare @moulds varchar(4000);set @moulds='55-480730-03,
55-487780-01,
,
55-487780-02';declare @table1 table(col1 nvarchar(4000));declare @table2 table(col1 nvarchar(40), pos int);insert into @table1 values(replace(@moulds, char(13)+char(10), ''));select * from @table1;insert into @table2
select rtrim(ltrim(replace(substring(A.col1, B.number, charindex(',', A.col1+',', B.number)-B.number) , char(9), ''))) as col2, B.number
from @table1Ainner joinmaster..spt_values Bon charindex(',', ','+A.col1, B.number)=B.number
where B.type='P';select * from @table2;
结果:
2、拆多列数据:
有如下数据表
需求就是将Col1,Col2按照特定的字符串分割成多行
先将该字段值统一替换为逗号分割,再将逗号分割替换转为XML数据类型,再利用xml转为多个行
declare @table1 table(
IDint,
Col1nvarchar(50) ,
Col2nvarchar(50)
);insert into @table1 values ( 1, 'a,b,c', '诶,必,塞,地,伊');insert into @table1 values ( 2, 'w', N'三四,不知道咧');--方式一
selecta.ID, a.Col1, a.Col2, v1, v2from ( select ID, Col1, Col2, convert(xml, '' + replace(replace(Col1, ',', ','), ',', '') + '') asxmlval1 ,convert(xml, '' + replace(replace(Col2, ',', ','), ',', '') + '') asxmlval2from @table1) across apply ( select k.n.value('.', 'nvarchar(80)') v1from a.xmlval1.nodes('n') k(n) ) bscross apply ( select k.n.value('.', 'nvarchar(80)') v2from a.xmlval2.nodes('n') k(n) ) ns;--方式二
selectID, t.Col1,t.Col2, v1, v2from @table1 astcross apply ( values (convert(xml, '' + replace(replace(Col1, ',', ','), ',', '')+ ''),convert(xml, '' + replace(replace(Col2, ',', ','), ',', '')+ ''))
) a (xmlval1 , xmlval2 )cross apply ( select k.n.value('.', 'varchar(80)') asv1from a.xmlval1.nodes('n') k(n)) bscross apply ( select k.n.value('.', 'varchar(80)') asv2from a.xmlval2.nodes('n') k(n) ) ns;
3、创建自定义拆分函数
函数功能:切分字符串, 返回一个列名为id的表
--1. 创建fn_Split函数
IF EXISTS(SELECT *
FROMdbo.sysobjectsWHERE id = OBJECT_ID('fn_Split')AND (TYPE = 'FN' OR TYPE = 'TF' OR TYPE = 'IF')
)DROP FUNCTIONfn_SplitGO
CREATE FUNCTION [dbo].[fn_Split](@str VARCHAR(MAX),@separator VARCHAR(10)
)RETURNS TABLE
AS
RETURN(
SELECTB.idFROM(
(--A 的作用只是生成 'abdc' 的XML格式的数据, 提供数据源
SELECT [value] = CONVERT(XML, '' + REPLACE(@str, @separator, '') + '')
) AOUTERAPPLY
(--B 的作用是将A中的 XML 数据的值枚举出来转换成行
SELECT id = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/v') N(v)
) B
)
)GO
使用函数 SELECT id FROM fn_Split('a,b,d,c',',')
declare @moulds varchar(4000);set @moulds='55-480730-03,
55-487780-01,
,
55-487780-02';declare @table1 table(id INT,col1 nvarchar(MAX));INSERT INTO @table1 VALUES(1,replace(@moulds, char(13)+char(10), ''))INSERT INTO @table1 VALUES(2,replace(@moulds, char(13)+char(10), ''))select * from @table1;SELECT a.id,rtrim(ltrim(replace(b.id, char(10), '') )) ASitemFROM @table1 a CROSS APPLY dbo.fn_Split(a.col1,',')ASbwhere b.id !=''
4、SQL Server 2016新增了string_split函数
专门用来拆分字符串。
SELECTt.id,
t.name,
t.description,
v.valueFROMtest tCROSS APPLY STRING_SPLIT(t.description, ',')v;