SQL Server:拆分记录 [英] SQL Server: split record
问题描述
我有一个这样的表:
account | check1 | check2
1 | 100]200]300 | 101]209]305
2 | 401]502 | 404]511
3 | 600 | 601
我想将记录分成这样:
account | check1 | check2
1 | 100 | 101
1 | 200 | 209
1 | 300 | 305
2 | 401 | 404
2 | 502 | 511
. | . | .
. | . | .
. | . | .
如何仅使用 SQL服务器来做到这一点?
How do I do this using SQL server only?
谢谢
推荐答案
首先,您需要一个split函数,该函数可以让您确定结果中的顺序.这是使用IDENTITY列
First, you need a split function that can allow you to determine order within the result. This is a multi-statement TVF which uses an IDENTITY column
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS @t TABLE(ID INT IDENTITY(1,1), Item INT)
AS
BEGIN
INSERT @t(Item) SELECT SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
ORDER BY Number OPTION (MAXDOP 1);
RETURN;
END
GO
(如果您有一个Numbers表,则可以使用它代替子查询,这还将使您可以在函数的定义中添加WITH SCHEMABINDING,这可能会带来性能上的好处.)
(If you have a Numbers table, you can use that instead of the subquery, and this will also allow you to add WITH SCHEMABINDING to the function's definition, which provides potential performance benefits.)
使用该功能后,此处给出了您提供的数据和所需结果的示例用法:
With the function in place, here is sample usage given the data you've provided and desired results:
DECLARE @x TABLE(account INT, check1 NVARCHAR(1000), check2 NVARCHAR(1000));
INSERT @x SELECT 1, '100]200]300','101]209]305'
UNION ALL SELECT 2, '401]502','404]511'
UNION ALL SELECT 3, '600','601'
UNION ALL SELECT 4, '205]104','304]701'; -- I added this sanity check
SELECT account, check1 = s1.Item, check2 = s2.Item
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.check1, ']') AS s1
CROSS APPLY dbo.SplitStrings(x.check2, ']') AS s2
WHERE s1.ID = s2.ID
ORDER BY account, s1.ID;
结果:
account check1 check2
------- ------ ------
1 100 101
1 200 209
1 300 305
2 401 404
2 502 511
3 600 601
4 205 304
4 104 701
这假定您具有某种验证/强制执行,即check1和check2列中的对应值将始终具有相同数量的值.它还假定任何check1/check2值都不会超过7,000个字符(同样,Numbers表可以帮助使其更灵活).
This assumes that you have some kind of validation / enforcement that corresponding values in check1 and check2 columns will always have the same number of values. It also assumes any check1 / check2 value will not exceed about 7,000 characters (again a Numbers table can help make that more flexible).
编辑
在AndriyM发表评论后,我想返回并重新访问它,主要是为了提供上述功能的一个版本,该版本可以在不使用多语句TVF的情况下工作.这使用了安德里(Andriy)的想法ROW_NUMBER().
After AndriyM's comments I wanted to come back and re-visit this, mostly to supply a version of the above function which works without using a multi-statement TVF. This uses Andriy's idea ROW_NUMBER() could be used.
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);
GO
这篇关于SQL Server:拆分记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!