根据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;
结果:
它将由指定的分隔符分隔将字符串行连接成一个字符串。 它不会在结果字符串的末尾添加分隔符。
SELECT name, string_agg(code,";") files FROM @table1 GROUP BY name;
将如下从Excel复制的一栏数据,插入到表中行进显示(同时去掉回车换行符,空白和Tab符号):
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;
结果:
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;
结果:
有如下数据表
需求就是将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, 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;
函数功能:切分字符串, 返回一个列名为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 TABLE AS RETURN ( SELECT B.id FROM ( ( --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 b where b.id !=""
专门用来拆分字符串。
SELECT t.id, t.name, t.description, v.value FROM test t CROSS APPLY STRING_SPLIT(t.description, ",")v;
到此这篇关于SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
相关文章: