拆分字符串然后旋转结果 [英] Splitting a string then pivoting result
本文介绍了拆分字符串然后旋转结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果从 .Net 应用程序传递一个字符串,如下所示
If have a string passed from a .Net application that looks like the below
2023|F66451,1684|648521,1684|600271,2137|019592
我已经开始使用下面的方法解析出字符串,但我需要旋转从 Split(被 * 包围)函数返回的数据,以便插入到 #tmpExceptions 表中
I have started to parse out the string using the method below but I need to Pivot the data returned from the Split ( surrounded by *'s) function in order to insert into the #tmpExceptions table
DECLARE @ExceptionsList as nvarchar(MAX)
SET @ExceptionsList = '2023|F66451,1684|648521,1684|600271,2137|019592'
SET NOCOUNT ON;
DECLARE @CurrentLineItem as nvarchar(255)
CREATE TABLE #ParsePassOne
(
LineItem nvarchar(255)
)
CREATE TABLE #tmpExceptions
(
AccountNumber int,
ClaimNumber nvarchar(50)
)
INSERT INTO #ParsePassOne
SELECT value FROM Split( ',' ,@ExceptionsList)
WHILE EXISTS(SELECT LineItem FROM #ParsePassOne)
BEGIN
SELECT TOP 1 @CurrentLineItem = LineItem FROM #ParsePassOne
*******
SELECT value FROM Split( '|' ,@CurrentLineItem)
*******
DELETE FROM #ParsePassOne WHERE LineItem = @CurrentLineItem
END
SELECT * FROM #tmpExceptions
DROP TABLE #ParsePassOne
DROP TABLE #tmpExceptions
到目前为止返回的数据如下所示.我只需要将数据透视到列,这样我就可以插入它.我该怎么办?
So far the data returned looks as below. I just need to pivot the data to columns so I can insert it. How do I go about this?
分割函数
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Creates an 'InLine' Table Valued Function (TVF)
ALTER FUNCTION [dbo].[Split]
( @Delimiter varchar(5),
@List varchar(8000)
)
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
[Value] varchar(50)
)
AS
BEGIN
DECLARE @LenString int
WHILE len( @List ) > 0
BEGIN
SELECT @LenString =
(CASE charindex( @Delimiter, @List )
WHEN 0 THEN len( @List )
ELSE ( charindex( @Delimiter, @List ) -1 )
END
)
INSERT INTO @TableOfValues
SELECT substring( @List, 1, @LenString )
SELECT @List =
(CASE ( len( @List ) - @LenString )
WHEN 0 THEN ''
ELSE right( @List, len( @List ) - @LenString - 1 )
END
)
END
RETURN
END
推荐答案
你可以用
select *
from
(
select * from #parsepassone
cross apply dbo.Split( '|' ,lineitem)
) src
pivot
(max(value) for rowid in ([1],[2]))p
或者用
insert #tmpExceptions (AccountNumber, ClaimNumber)
select [1],[2]
from
(
select e.rowid e, p.* from dbo.Split( ',' ,@ExceptionsList) e
cross apply dbo.Split( '|' ,e.value) p ) s
pivot
(max(value) for rowid in ([1],[2]))p
这篇关于拆分字符串然后旋转结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文