SQL Server一個字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個字符串
發(fā)布日期：2022-07-15 19:54 | 文章來源：站長之家
概述
- 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 a
group 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ù)，插入到表中行進(jìn)顯示（同時去掉回車換行符，空白和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.xmlval1
from (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) ) bs
where 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 @table2
select rtrim(ltrim(replace(substring(A.col1, B.number, charindex(',', A.col1+',', B.number)-B.number) , char(9), '') )) as col2, B.number
from @table1 A
inner join master..spt_values B
on charindex(',', ','+A.col1, B.number)=B.number
where 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, v2
from ( 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, v2
from @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 TABLE
AS
RETURN
(
SELECT B.id
FROM (
( --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 b where b.id !=''
4、SQL Server 2016新增了string_split函數(shù)
專門用來拆分字符串。
SELECT t.id,
t.name,
t.description,
v.value
FROM test t
CROSS APPLY STRING_SPLIT(t.description, ',')v;到此這篇關(guān)于SQL Server一個字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個字符串的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持本站。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信