文章详情页
SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串
概述
- 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 @table1 b where a.name=b.name for xml path("")), 1, 1, "")from @table1 agroup 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 @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;
结果:
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;
结果:
2、拆多列数据:
有如下数据表
需求就是将Col1,Col2按照特定的字符串分割成多行
先将该字段值统一替换为逗号分割,再将逗号分割替换转为XML数据类型,再利用xml转为多个行
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、创建自定义拆分函数
函数功能:切分字符串, 返回一个列名为id的表
--1. 创建fn_Split函数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格式的数据, 提供数据源 SELECT [value] = CONVERT(XML, "<v>" + REPLACE(@str, @separator, "</v><v>") + "</v>") ) A OUTER APPLY ( --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), "") )) AS item FROM @table1 a CROSS APPLY dbo.fn_Split(a.col1,",") AS bwhere b.id !=""
4、SQL Server 2016新增了string_split函数
专门用来拆分字符串。
SELECT t.id, t.name, t.description, v.valueFROM test t CROSS APPLY STRING_SPLIT(t.description, ",")v;
到此这篇关于SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
标签:
MsSQL
相关文章:
1. SQL Server修改数据的几种语句详解2. SQL Server还原完整备份和差异备份的操作过程3. SQL Server 7.0 入门(四)4. 八步解决ACCESS自动编号问题(将SQL SERVER 2000数据库,转换为ACCESS数据库)5. 用一个案例讲解SQL Server数据库恢复6. 榨干MS SQL Server 最后一滴血7. sql server 2005中的output子句8. SQL Server 2000中生成XML的小技巧9. SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询10. Microsoft SQL Server 7.0安装问题(一)
排行榜