微信扫一扫2.1w+
社区成员
sqlserver 如何将多列字符串 分割成多行数据。
问题比较难以描述。直接上图，有如下数据表
需求就是按照特定的字符串分割并对应 变成如下数据
尽量不要使用存储函数等。因为没有权限。
需求就是按照特定的字符串分割并对应 变成如下数据
尽量不要使用存储函数等。因为没有权限。
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
道素 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
这个方法 也想过了，但是现在就是不敢确定的就是解析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])
- 打赏
- 举报
ESinker 2017-11-03
这个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
- 打赏
- 举报
SQLServer将数据表中一行数据根据某个字符串分割成多行 select Ids=substring(a.Ids,b.number,charindex(',',a.Ids+',',b.number)-b.number) from table_1 a join master..spt_values b on b.type='P' where charindex(',',','+a.Ids,b.number)=b.number --在列前拼接上...
sql server 根据特定字符串拆分成多行 Set ARITHABORT ON select a.*, b.txtId from (select *, txt=convert(xml, '' + replace(HISOrderID, '#', '') + '') from T_Order) a ...C(v)) b HISOrderID：需要拆分的字符串字段 T_Order：为表名 #：拆分的字符
SQLServer 以逗号分割的字符串转换成多条数据 开发过程中，经常会用到用逗号分隔的字段，这时候如果用where xx in 的时候，如果把字段取出再拼接字符串的话，未免太麻烦， 网上有几种方法，值得考虑。 方法一：Sql Server XML方法： SELECT --列名 --...
server多笔记录拼接字符串 sql_sqlserver 将多行数据查询合并为一条数据 有这样一个需求：表T_FUN_TASK为任务表，有字段(TASKID,TASKNAME)，表T_...在查询表T_FUN_TASK时，需将任务表中的 LOGDATE 查询出来作为一列 LOGDATES显示。T_FUN_TASKT_FUN_LOGBOOK查询结果此结果查询方法可以用存...
SQL Server：多值字段分成多行显示 或者 多行数据合并成一行 需求就是将Col1，Col2按照特定的字符串分割成多行 回到顶部 一、利用XML解析方式 先将该字段值统一替换为逗号分割，再将逗号分割替换转为XML数据类型，再利用xml转为多个行 declare @table1 table ( ID ...
处理sql字符串切割，如（1，2；3，4切割成两行两列的数据）的存储过程 用于处理sql切割字符串，例如字符串1，2；3，4切割存入数据库为： 第一列 1 2 第二列 3 4 如果表中有三列，直接录入三列的字符串即可，如果只要录入特定的某些字段值，设置其他字段为默认的数值即可。 该存储过程...
sqlserver 数据多行以逗号分隔成一行 --按字段分组，将该组下某个字段多行以逗号拼接成一行 CREATE TABLE t1 ( mid INT, uid VARCHAR(1) ) insert into t1 values (1,'a') insert into t1 values (1,'b') insert into t1 values (1,'b') insert into ...
拼接一个字段的多条记录&字符串分割成多条记录（sqlserver） 1、拼接一列数据为一个字符串 -- 把tableName表的colName列（字符串类型）的值 按 逗号拼接，结果名为name select stuff((select ','+ colName from tableName for xml path ('')),1,1,'') as names 2、以逗号...
mssql sqlserver 将逗号分隔的一列数据转换为多列数据的方法分享 摘要: 下文讲述sqlserver中将使用逗号组合的单列数据，...使用xml读取函数读取相关数据例: 将包含逗号字符串拆分为多列的示例 create table [maomao365.com] (info varchar(200)) go ---生成基础数据 insert into...
servers split sql_SQLServer实现split分割字符串到列 网上已有人实现sqlserver的split函数可将字符串分割成行，但是我们习惯了split返回数组或者列表，因此这里对其做一些改动，最终实现也许不尽如意，但是也能解决一些问题。ALTER FUNCTION dbo.splitl (@String ...
Sqlserver根据某字段分隔符将一条记录拆分为多行记录 http://blog.sina.com.cn/s/blog_b3eabfd30102wldv.html 转载于:https://www.cnblogs.com/garfield211/p/6181831.html
SQL将一行的某列数据进行特殊字符拆分，并分为多行显示 要增加一个分割表值函数 IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL DROP FUNCTION [dbo].[Fun_String2ToStringArray] GO CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR...
mysql一个字段根据符号拆分_sql server中将一个字段根据某个字符拆分成多个字段显示... 要返回的对象部分。object_piece的数据类型为int值，可以为下列值：1 = 对象名称2 = ...第二种方案：思路：写一个函数 根据特定的分割符号利用SUBSTRING和CHARINDEX将字段进行截取拆分，--这个函数 返回的是一个表A...
SQLServer IN查询 字符串中的拼接ID 的数据集 --ID字符串：7fc9feee-3470-4e62-b50f-c42662a7d234,9f0fc00e-116e-4722-bda8-8b1308192122, --操作DECLARE @str VARCHAR(200)='' SET @str=(SELECT TOP 1 ScreenIds FROM dbo.SelfHelpMachine ...
