sqlserver 如何将多列字符串 分割成多行数据。

ESinker 2017-11-02 02:30:11
问题比较难以描述。直接上图，有如下数据表



需求就是按照特定的字符串分割并对应 变成如下数据



尽量不要使用存储函数等。因为没有权限。
...全文
2124 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2017-11-03
  • 打赏
  • 举报
 回复
你是说不要其他辅助表，纯粹用SQL？ 

WITH testdata(id,value,[name]) AS (
   SELECT 1,'23|24|25|26|29',N'啊，我，额，医，五' UNION ALL
   SELECT 1,'33|aa|bb|cc|ss',N'波，破，摸，佛，各' UNION ALL
   SELECT 2,'35|BB|CC|YY|RR',N'诶，必，塞，地，伊' UNION ALL
   SELECT 2,'56',N'我六' UNION ALL 
   SELECT 3,'34|ab',N'三四，不知道咧' UNION ALL
   SELECT 3,'58',N'舞吧'
)
SELECT id,t.value,t.name,v,n FROM testdata AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.value,'|','</n><n>')+'</n>'),(CONVERT(XML,'<n>'+REPLACE(t.[name],'，','</n><n>')+'</n>')))) c(x1,x2)
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','varchar(100)') AS v FROM c.x1.nodes('n')x(n)) vs
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','nvarchar(100)') AS n FROM c.x2.nodes('n')x(n)) ns
WHERE vs.rn=ns.rn


+----+----------------+-----------+----+------+
| id | value          | name      | v  | n    |
+----+----------------+-----------+----+------+
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 23 | 啊    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 24 | 我    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 25 | 额    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 26 | 医    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 29 | 五    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | 35 | 诶    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | BB | 必    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | CC | 塞    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | YY | 地    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | RR | 伊    |
| 2  | 56             | 我六        | 56 | 我六   |
| 3  | 34|ab          | 三四，不知道咧   | 34 | 三四   |
| 3  | 34|ab          | 三四，不知道咧   | ab | 不知道咧 |
| 3  | 58             | 舞吧        | 58 | 舞吧   |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | 33 | 波    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | aa | 破    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | bb | 摸    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | cc | 佛    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | ss | 各    |
+----+----------------+-----------+----+------+
吉普赛的歌 2017-11-03
  • 打赏
  • 举报
 回复
其它的种种方式， 都不适合你， SQL写得再好， 也不如现成的数据摆在那里。 设计很重要， 没弄好就是一步错步步错。
ESinker 2017-11-03
  • 打赏
  • 举报
 回复
引用 4 楼 ch21st 的回复:
你是说不要其他辅助表，纯粹用SQL？ 

WITH testdata(id,value,[name]) AS (
   SELECT 1,'23|24|25|26|29',N'啊，我，额，医，五' UNION ALL
   SELECT 1,'33|aa|bb|cc|ss',N'波，破，摸，佛，各' UNION ALL
   SELECT 2,'35|BB|CC|YY|RR',N'诶，必，塞，地，伊' UNION ALL
   SELECT 2,'56',N'我六' UNION ALL 
   SELECT 3,'34|ab',N'三四，不知道咧' UNION ALL
   SELECT 3,'58',N'舞吧'
)
SELECT id,t.value,t.name,v,n FROM testdata AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.value,'|','</n><n>')+'</n>'),(CONVERT(XML,'<n>'+REPLACE(t.[name],'，','</n><n>')+'</n>')))) c(x1,x2)
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','varchar(100)') AS v FROM c.x1.nodes('n')x(n)) vs
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','nvarchar(100)') AS n FROM c.x2.nodes('n')x(n)) ns
WHERE vs.rn=ns.rn


+----+----------------+-----------+----+------+
| id | value          | name      | v  | n    |
+----+----------------+-----------+----+------+
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 23 | 啊    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 24 | 我    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 25 | 额    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 26 | 医    |
| 1  | 23|24|25|26|29 | 啊，我，额，医，五 | 29 | 五    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | 35 | 诶    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | BB | 必    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | CC | 塞    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | YY | 地    |
| 2  | 35|BB|CC|YY|RR | 诶，必，塞，地，伊 | RR | 伊    |
| 2  | 56             | 我六        | 56 | 我六   |
| 3  | 34|ab          | 三四，不知道咧   | 34 | 三四   |
| 3  | 34|ab          | 三四，不知道咧   | ab | 不知道咧 |
| 3  | 58             | 舞吧        | 58 | 舞吧   |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | 33 | 波    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | aa | 破    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | bb | 摸    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | cc | 佛    |
| 1  | 33|aa|bb|cc|ss | 波，破，摸，佛，各 | ss | 各    |
+----+----------------+-----------+----+------+
这个方法 也想过了，但是现在就是不敢确定的就是解析xml时的顺序是怎么样的。因为查了官方文档找不到有关的说明，所以没有敢擅自使用
吉普赛的歌 2017-11-03
  • 打赏
  • 举报
 回复
这个东西从最开始就错了。 如果你追求性能， 一开始就不应该设计成这样。按数据库规范来说也不能这样设计。 当然， 也不是一无是处， 没性能要求的情况下， 这样做也无可厚非，只不过不适合你目前的情况了。 增加2个表就可以了。 
IF OBJECT_ID('value_mid') IS NOT NULL DROP TABLE value_mid
CREATE TABLE value_mid (
	mid INT IDENTITY(1,1),
	id INT NOT NULL,
	[value] NVARCHAR(20) NOT NULL,
	CONSTRAINT PK_value_mid PRIMARY KEY NONCLUSTERED(
		mid
	)
)
CREATE CLUSTERED INDEX IX_value_mid_id_vlue ON value_mid(id,[value])

IF OBJECT_ID('name_mid') IS NOT NULL DROP TABLE name_mid
CREATE TABLE name_mid (
	mid INT IDENTITY(1,1),
	id INT NOT NULL,
	[name] NVARCHAR(20) NOT NULL,
	CONSTRAINT PK_name_mid PRIMARY KEY NONCLUSTERED(
		mid
	)
)
CREATE CLUSTERED INDEX IX_name_mid_id_vlue ON name_mid(id,[name])
增加完之后， 把原表中的值按切分函数切分后插入到这两个表。 此外， 以后插入原表时， 也要往这2个表里插入一份。 这样就清净了， 以你想查啥， 直接查这两个表就是。
ESinker 2017-11-03
  • 打赏
  • 举报
 回复
引用 3 楼 RINK_1 的回复:
你前两天不是问过类似的问题了吗，那些方法不行？你说的不能用存储函数，指的是存储过程还是一些系统表，比如spt_values。如果是这个表，那你自己去构造一个序号辅助表，然后用这个表去进行处理。 


WITH TABLE_SEQ
AS
(SELECT 0 AS NUMBER
 UNION ALL
 SELECT NUMBER+1 FROM TABLE_SEQ
 WHERE NUMBER+1<=2000),

CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS SEQ_1
 FROM TABLE)
 

SELECT A.ID,A.single_VALUE AS VALUE_N,B.single_NAME AS NAME_N
FROM
(SELECT A.ID,SEQ_1,
       SUBSTRING(VALUE_N,B.NUMBER,CHARINDEX('|',VALUE_N+'|',B.NUMBER)-B.NUMBER) AS single_VALUE,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX('|','|'+A.VALUE_N,B.number)=B.number ) AS A 
JOIN
(SELECT A.ID,SEQ_1,
       SUBSTRING(NAME_N,B.NUMBER,CHARINDEX(',',NAME_N+',',B.NUMBER)-B.NUMBER) AS single_NAME,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX(',',','+A.NAME_N,B.number)=B.number ) AS B
ON A.ID=B.ID AND A.SEQ_1=B.SEQ_1 AND A.SEQ_2=B.SEQ_2  
ORDER BY A.ID,A.SEQ_1,B.SEQ_2
OPTION(MAXRECURSION 0)
这个sql 已经写出来了。但是由于数据量问题。性能很差。希望看看更多的解决方案
RINK_1 2017-11-02
  • 打赏
  • 举报
 回复
你前两天不是问过类似的问题了吗，那些方法不行？你说的不能用存储函数，指的是存储过程还是一些系统表，比如spt_values。如果是这个表，那你自己去构造一个序号辅助表，然后用这个表去进行处理。 


WITH TABLE_SEQ
AS
(SELECT 0 AS NUMBER
 UNION ALL
 SELECT NUMBER+1 FROM TABLE_SEQ
 WHERE NUMBER+1<=2000),

CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS SEQ_1
 FROM TABLE)
 

SELECT A.ID,A.single_VALUE AS VALUE_N,B.single_NAME AS NAME_N
FROM
(SELECT A.ID,SEQ_1,
       SUBSTRING(VALUE_N,B.NUMBER,CHARINDEX('|',VALUE_N+'|',B.NUMBER)-B.NUMBER) AS single_VALUE,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX('|','|'+A.VALUE_N,B.number)=B.number ) AS A 
JOIN
(SELECT A.ID,SEQ_1,
       SUBSTRING(NAME_N,B.NUMBER,CHARINDEX(',',NAME_N+',',B.NUMBER)-B.NUMBER) AS single_NAME,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX(',',','+A.NAME_N,B.number)=B.number ) AS B
ON A.ID=B.ID AND A.SEQ_1=B.SEQ_1 AND A.SEQ_2=B.SEQ_2  
ORDER BY A.ID,A.SEQ_1,B.SEQ_2
OPTION(MAXRECURSION 0)
听雨停了 2017-11-02
  • 打赏
  • 举报
 回复 

CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
    AS 
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
                            + '</v>')
                ) A
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')
                    FROM    A.[value].nodes('/v') N ( v )
                  ) B
    )
执行上面的代码得先创建这个函数
听雨停了 2017-11-02
  • 打赏
  • 举报
 回复 


--测试数据

IF OBJECT_ID('tempdb..#tab') IS NOT NULL

	DROP TABLE #tab

CREATE TABLE #tab(

id INT,

VALUE VARCHAR(20),

NAME VARCHAR(20)	

)

INSERT INTO #tab

SELECT 1,'12|23','你好,hello' union all

SELECT 1,'456|1|35','Abc,efc,kkk' UNION ALL

SELECT 2,'12','pppp'

--测试数据结束



IF OBJECT_ID('tempdb..#t') IS NOT NULL

	DROP TABLE #t

SELECT a.id AS id_mstr,a.[VALUE],a.name,b.*,identity (int,1,1) as rn into #t FROM #tab a

OUTER APPLY dbo.ufn_SplitStringToTable(a.[VALUE],'|') b

IF OBJECT_ID('tempdb..#t2') IS NOT NULL

	DROP TABLE #t2

SELECT a.id AS id_mstr,a.[VALUE],a.name,b.*,identity (int,1,1) as rn into #t2 FROM #tab a

OUTER APPLY dbo.ufn_SplitStringToTable(a.name,',') b



SELECT a.id_mstr,

       a.value,

       a.name,

       a.id  AS value_n,

       b.id  AS name_n

FROM   #t a

       INNER JOIN #t2 b

            ON  a.rn = b.rn

            AND a.id_mstr = b.id_mstr

            AND a.value = b.value

SQL 语句 将一个表中用特殊字符分割的字段转换成多行数据.docx
找了网上很多资料，都没有详细的做法有的还很复杂，自己写了个分割函数，通过直接查询就能分割多行，很方便。
SQL SERVER 根据关键字符,将一列分成多行
可根据关键字符将一列分成多行显示,例如 A B 1 1.1.2 拆分成 1 1 1 1 1 2 进行相关的显示
sqlserver中根据字符分割字符串的最好的写法分享
因数据库中保存的是以,号分隔的数据，需要在界面上以表格的方式显示出来。特想出以下方法
SQL 2005常用语句
收集了 几十个 SQL2005 的常用语句
sql大全（本人多年积累精华）
sql大全（本人多年积累精华） sql大全（本人多年积累精华） sql大全（本人多年积累精华）
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章