概述

• STRING_AGG（合并）：多行數(shù)據(jù)合并成一個字符串，以逗號隔開。
• STRING_SPLIT（拆分）:一個字符串，拆分成多行。

一、多行數(shù)據(jù)合并成一個字符串

1、通過 FOR xml path('') 合并字符串記錄

根據(jù)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 @table1 b  where a.name=b.name for xml path("")), 1, 1, "")from @table1 agroup by name;

結(jié)果：

2、MS SQL Server的2017新增了STRING_AGG()是一個聚合函數(shù)

它將由指定的分隔符分隔將字符串行連接成一個字符串。 它不會在結(jié)果字符串的末尾添加分隔符。

SELECT  name,   string_agg(code,";") files FROM @table1 GROUP BY name;

二、一個字符串拆分成多行

1、拆一列數(shù)據(jù)：

將如下從Excel復(fù)制的一欄數(shù)據(jù)，插入到表中行進顯示（同時去掉回車換行符，空白和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 @table1insert into @table2  select   rtrim(ltrim(replace(bs.v1, char(9), "") )),a.xmlval1from (select convert(xml, "<n>"+replace(replace(col1, "，", ","), ",", "</n><n>")+"</n>") as xmlval1         from @table1) a       cross apply(select k.n.value(".", "nvarchar(80)") v1 from a.xmlval1.nodes("n") k(n) ) bswhere bs.v1 !="";select * from @table2;

結(jié)果：

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 @table2select rtrim(ltrim(replace(substring(A.col1, B.number, charindex(",", A.col1+",", B.number)-B.number) , char(9), "") )) as col2, B.numberfrom @table1 A     inner join master..spt_values B on charindex(",", ","+A.col1, B.number)=B.numberwhere B.type="P";select * from @table2;

結(jié)果：

2、拆多列數(shù)據(jù)：

有如下數(shù)據(jù)表

需求就是將Col1，Col2按照特定的字符串分割成多行

先將該字段值統(tǒng)一替換為逗號分割，再將逗號分割替換轉(zhuǎn)為XML數(shù)據(jù)類型，再利用xml轉(zhuǎn)為多個行

declare @table1 table    (ID int ,Col1 nvarchar(50) ,Col2 nvarchar(50)    );insert into @table1 values ( 1, "a,b,c", "誒，必，塞，地，伊" );insert into @table1 values ( 2, "w", N"三四，不知道咧" );--方式一select a.ID, a.Col1, a.Col2,  v1, v2from   (   select ID, Col1, Col2, convert(xml, "<n>" + replace(replace(Col1, "，", ","), ",", "</n><n>") + "</n>") as xmlval1 ,  convert(xml, "<n>" + replace(replace(Col2, "，", ","), ",", "</n><n>") + "</n>") as xmlval2   from   @table1 ) a       cross apply (   select k.n.value(".", "nvarchar(80)") v1       from   a.xmlval1.nodes("n") k(n) ) bs       cross apply (   select k.n.value(".", "nvarchar(80)") v2       from   a.xmlval2.nodes("n") k(n) ) ns;--方式二select ID, t.Col1,t.Col2,  v1, v2from   @table1 as t       cross apply ( values (convert(xml, "<n>" + replace(replace(Col1, "，", ","), ",", "</n><n>")+ "</n>"),      convert(xml, "<n>" + replace(replace(Col2, "，", ","), ",", "</n><n>")+ "</n>"))   ) a (xmlval1 , xmlval2 )       cross apply (   select k.n.value(".", "varchar(80)") as v1       from   a.xmlval1.nodes("n") k(n)) bs       cross apply (   select k.n.value(".", "varchar(80)") as v2       from   a.xmlval2.nodes("n") k(n) ) ns;

3、創(chuàng)建自定義拆分函數(shù)

函數(shù)功能：切分字符串， 返回一個列名為id的表

--1. 創(chuàng)建fn_Split函數(shù)IF EXISTS(       SELECT *       FROM   dbo.sysobjects       WHERE  id = OBJECT_ID("fn_Split")      AND (TYPE = "FN" OR TYPE = "TF" OR TYPE = "IF")   )    DROP FUNCTION fn_Split  GO     CREATE FUNCTION [dbo].[fn_Split](    @str   VARCHAR(MAX),    @separator     VARCHAR(10))RETURNS TABLEAS    RETURN     (SELECT B.idFROM   (   (  --A　的作用只是生成 "<v>a</v><v>b</v><v>d</v><v>c</v>" 的XML格式的數(shù)據(jù), 提供數(shù)據(jù)源        SELECT [value] = CONVERT(XML, "<v>" + REPLACE(@str, @separator, "</v><v>") + "</v>")   ) A    OUTER APPLY   (  --B 的作用是將A中的 XML 數(shù)據(jù)的值枚舉出來轉(zhuǎn)換成行       SELECT id = N.v.value(".", "varchar(100)") FROM   A.[value].nodes("/v") N(v)   ) B       )    )GO

使用函數(shù) 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), "") )) AS item FROM @table1 a CROSS APPLY dbo.fn_Split(a.col1,",") AS bwhere b.id !=""

4、SQL Server 2016新增了string_split函數(shù)

專門用來拆分字符串。

SELECT t.id,       t.name,       t.description,       v.valueFROM test t    CROSS APPLY STRING_SPLIT(t.description, ",")v;

到此這篇關(guān)于SQL Server一個字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個字符串的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持。