SQL Server将行拆分为多行
问题描述:
我正在研究将一行拆分为多行的查询。 我有以下为我查询的结果:SQL Server将行拆分为多行
TEXTVALUE
1 Product1 P5 Product2 P19 Product3 P30 Product4 P11 Product5 P23
2 Product3 P30 Product7 P33
3 Product8 P53 Product5 P23 Product1 P5
我需要输出到这一点:
PRODUCT AMOUNT
1 Product1 P5
2 Product1 P5
3 Product2 P19
4 Product3 P30
5 Product3 P30
6 Product4 P11
7 Product5 P23
8 Product5 P23
9 Product7 P33
10 Product8 P53
不管重复的,我只需要在产品名称和之间基本上其拆分量。
请指点好吗?
非常感谢!
答
我倾向于选择上面链接的文章中所列的方法,称为Moden拆分器。我不使用Aaron修改的版本,以便他的比较一致。我使用这里发布的原件。 http://www.sqlservercentral.com/articles/Tally+Table/72993/您需要查看该页面并在此代码生效之前获取该分隔线的代码。
分离器比其他大多数分离器具有的一大优点是它可以返回单个项目的行号。你可以很好地利用这个多层次的分割,就像你在这里需要的一样。
这是一个使用计数表分离器的完整工作示例。在这个简单的例子
if OBJECT_ID('tempdb..#something')is not null
drop table #Something
create table #Something
(
SomeID int identity,
TextValue varchar(500)
)
insert #Something
select 'Product1 P5 Product2 P19 Product3 P30 Product4 P11 Product5 P23' union all
select 'Product3 P30 Product7 P33' union all
select 'Product8 P53 Product5 P23 Product1 P5';
with MySplitData as
(
select Item
, ItemNumber
, SomeID
from #Something s
cross apply dbo.DelimitedSplit8K(s.TextValue, ' ') x
)
select p.Product
, a.Amount
from
(
select Item as Product
, ItemNumber
, SomeID
from MySplitData
where ItemNumber % 2 = 1
) p
join
(
select Item as Amount
, ItemNumber
, SomeID
from MySplitData
where ItemNumber % 2 = 0
) a on a.ItemNumber = p.ItemNumber + 1
AND a.SomeID = p.SomeID
order by p.SomeID
, p.ItemNumber
答
拆分可以通过使用FTS世界断路器(如果产品名称中含有短划线或其他字符,它可以迫使FTS解析器将其分成几个方面做 - 创建UDF的分裂,如建议在以前的答案)。使用LEAD窗口函数可以完成下一个值(SQL Server 2012和更高版本,这种方法只能执行一次表扫描)。
CREATE TABLE #t (text nvarchar(100))
GO
INSERT INTO #t VALUES
('Product1 P5 Product2 P19 Product3 P30 Product4 P11 Product5 P23'),
('Product3 P30 Product7 P33'),
('Product8 P53 Product5 P23 Product1 P5');
WITH SplittedData AS
(
SELECT t.display_term AS PRODUCT,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNumber,
LEAD(t.display_term, 1, NULL) OVER(ORDER BY (SELECT 1)) AS AMOUNT
FROM #t
CROSS APPLY sys.dm_fts_parser('"' + text + '"', 1033, NULL, 1) t
)
SELECT PRODUCT, AMOUNT
FROM SplittedData
WHERE RowNumber % 2 = 1
ORDER BY PRODUCT
DROP TABLE #t
GO
其中是以这种方式存储的数据?希望不在桌上?你在一个文件中得到这个吗?他们不能提供CSV或管道分隔文件吗?如果产品名称中包含P#,会发生什么情况?产品能有空间吗?有很多信息你没有提供,你需要考虑一下才能得到答案(我猜) – Kritner
好点。事情是他们把这个存储在一张桌子里。他们的软件将接受列表,然后将其作为一行存储在数据库中。我相信他们有验证可以处理任何不必要的字符。产品没有空间。他们基本上是ItemCodes,但我只是将它们表示为问题中的名称,所以它会更易于阅读。非常感谢! – Smiley
这只是一个可怕的方式来处理数据,因为它违反了1NF,并且只会导致大量的痛苦。您将不得不使用分离器将这些数据分成几行。 http://sqlperformance.com/2012/07/t-sql-queries/split-strings –