SQL Server一個(gè)字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個(gè)字符串
更新時(shí)間：2022年05月24日 10:34:27 作者：springsnow
這篇文章介紹了SQL Server一個(gè)字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個(gè)字符串的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
概述
- STRING_AGG（合并）：多行數(shù)據(jù)合并成一個(gè)字符串，以逗號(hào)隔開(kāi)。
- STRING_SPLIT（拆分）:一個(gè)字符串，拆分成多行。
一、多行數(shù)據(jù)合并成一個(gè)字符串
1、通過(guò) 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()是一個(gè)聚合函數(shù)
它將由指定的分隔符分隔將字符串行連接成一個(gè)字符串。 它不會(huì)在結(jié)果字符串的末尾添加分隔符。
SELECT name, string_agg(code,';') files FROM @table1 GROUP BY name;
二、一個(gè)字符串拆分成多行
1、拆一列數(shù)據(jù)：
將如下從Excel復(fù)制的一欄數(shù)據(jù)，插入到表中行進(jìn)顯示（同時(shí)去掉回車(chē)換行符，空白和Tab符號(hào)）：
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)一替換為逗號(hào)分割，再將逗號(hào)分割替換轉(zhuǎn)為XML數(shù)據(jù)類(lèi)型，再利用xml轉(zhuǎn)為多個(gè)行
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ù)功能：切分字符串， 返回一個(gè)列名為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ù)的值枚舉出來(lái)轉(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ù)
專(zhuān)門(mén)用來(lái)拆分字符串。
SELECT t.id,
t.name,
t.description,
v.value
FROM test t
CROSS APPLY STRING_SPLIT(t.description, ',')v;到此這篇關(guān)于SQL Server一個(gè)字符串拆分多行顯示或者多行數(shù)據(jù)合并成一個(gè)字符串的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
小系統(tǒng)單據(jù)自動(dòng)生成存儲(chǔ)過(guò)程
此處判斷有兩種方法：一種是根據(jù)傳入6位日期判斷；另一種根據(jù)單據(jù)創(chuàng)建日期字段（前提：表有創(chuàng)建時(shí)間字段）2010-07-07
MSSQL MySQL 數(shù)據(jù)庫(kù)分頁(yè)(存儲(chǔ)過(guò)程)
有關(guān)分頁(yè) SQL 的資料很多，有的使用存儲(chǔ)過(guò)程，有的使用游標(biāo)。本人不喜歡使用游標(biāo)，我覺(jué)得它耗資、效率低；使用存儲(chǔ)過(guò)程是個(gè)不錯(cuò)的選擇，因?yàn)榇鎯?chǔ)過(guò)程是經(jīng)過(guò)預(yù)編譯的，執(zhí)行效率高，也更靈活2012-01-01
SQL?Server日志清理3種實(shí)現(xiàn)方式
這篇文章主要給大家介紹了關(guān)于SQL?Server日志清理3種實(shí)現(xiàn)方式的相關(guān)資料,在數(shù)據(jù)庫(kù)使用很久后日志文件會(huì)累計(jì)的越來(lái)越大,如果硬盤(pán)空間不足可能會(huì)導(dǎo)致宕機(jī),需要的朋友可以參考下2023-09-09
在SQL Server中,時(shí)間和日期是常見(jiàn)的數(shù)據(jù)類(lèi)型,也是數(shù)據(jù)處理中重要的一部分,本文將介紹SQL Server中一些常用的時(shí)間和日期函數(shù),以及它們的用法和示例,感興趣的可以了解一下2023-10-10
在SQL Server中使用命令調(diào)用SSIS包的具體方法
在SQL Server中可以使用dtexec命令運(yùn)行SSIS包（2005以上版本）,當(dāng)然也可以通過(guò)系統(tǒng)過(guò)程:xp_cmdshell調(diào)用dtexec運(yùn)行SSIS包2013-09-09
SQL命令優(yōu)化需要記住的9點(diǎn)事項(xiàng)
SQL命令優(yōu)化這是個(gè)自從數(shù)據(jù)庫(kù)誕生的時(shí)候就開(kāi)始討論的話題，每個(gè)人都有自己的理解，當(dāng)然，本人也有些經(jīng)驗(yàn)，總結(jié)給大家，希望對(duì)大家有所幫助2014-08-08
sql 函數(shù)大全 比較常用的一些函數(shù)整理
學(xué)習(xí)sqlserver的朋友，可以參考下，掌握這些函數(shù)可以方便的對(duì)字段的字符進(jìn)行處理。2010-04-04
SQL Server中實(shí)現(xiàn)數(shù)據(jù)庫(kù)的自定義視圖
在SQL Server的數(shù)據(jù)處理和分析中,視圖（View）是一種虛擬的表,其內(nèi)容由SQL查詢定義,自定義視圖允許用戶根據(jù)需要?jiǎng)?chuàng)建個(gè)性化的數(shù)據(jù)展示,本文將詳細(xì)介紹如何在SQL Server中實(shí)現(xiàn)數(shù)據(jù)庫(kù)的自定義視圖,需要的朋友可以參考下2024-07-07
本文將介紹SQL Server中判斷和處理NULL值的不同方法，以及一些解決方案，幫助您更好地處理數(shù)據(jù)庫(kù)中的NULL值情況，感興趣的朋友跟隨小編一起看看吧2023-07-07
SqlServer開(kāi)發(fā)神器'SQLPrompt'插件的使用詳解
SQLPrompt是Sql?Server?開(kāi)發(fā)智能提示插件，方便查詢表結(jié)果，避免了開(kāi)發(fā)人員一個(gè)個(gè)敲查詢語(yǔ)句、執(zhí)行語(yǔ)句等，這篇文章主要介紹了SqlServer數(shù)據(jù)庫(kù)開(kāi)發(fā)神器'SQLPrompt插件'的使用說(shuō)明,需要的朋友可以參考下2023-03-03