sql-server - 旋转 SPLIT_STRING 的结果？

Question

我有一个表，其中有一列包含如下字符串:

/TYPE/BOOKING/IBAN/NL12BANK0003456789/BIC/BANKNL2A/NAME/Mr. A. Someguy/CODE/Codenumber 12345678/REF/NOTPROVIDED/LINE/ABCD EFG 234567890 1234 ETC
/TYPE/BOOKING/IBAN/NL34BANK000123456/BIC/BANKNL2U/NAME/Mr. A. Dinges/CODE/98765432/REF/NOTPROVIDED

我想在这些字符串中查找单个元素，而不必编写包含许多 CHARINDEX-es 和 SUBSTRINGS 的不可读代码。所以我找到了 SPLIT_STRING 函数。

select contract, [value]
from SCHEMA.PAYMENTS
    CROSS APPLY STRING_SPLIT(paymentrow, '/')

这很好，但现在我在行中得到了这些值:

bookingnumber   value
12-3-56789012-3 
12-3-56789012-3 TYPE
12-3-56789012-3 BOOKING
12-3-56789012-3 IBAN
12-3-56789012-3 NL12BANK0003456789
12-3-56789012-3 BIC
12-3-56789012-3 BANKNL2A
12-3-56789012-3 NAME
12-3-56789012-3 Mr. A. Someguy
12-3-56789012-3 CODE
12-3-56789012-3 Codenumber 12345678
12-3-56789012-3 REF
12-3-56789012-3 NOTPROVIDED
12-3-56789012-3 LINE
12-3-56789012-3 ABCD EFG 234567890 1234 ETC
98-7-65431234-0 
98-7-65431234-0 TYPE
98-7-65431234-0 BOOKING
98-7-65431234-0 IBAN
98-7-65431234-0 NL34BANK000123456
98-7-65431234-0 BIC
98-7-65431234-0 BANKNL2U
98-7-65431234-0 NAME
98-7-65431234-0 Mr. A. Dinges
98-7-65431234-0 CODE
98-7-65431234-0 98765432
98-7-65431234-0 REF
98-7-65431234-0 NOTPROVIDED

因此，作为最后一步，我将旋转值列，以便原始字符串中的元素显示为整齐的列，如下所示:

bookingnumber    type     IBAN                BIC       name            code                 ref
12-3-56789012-3  BOOKING  NL12BANK0003456789  BANKNL2A  Mr. A. Someguy  Codenumber 12345678  NOTPROVIDED
98-7-65431234-0  BOOKING  NL34BANK0001234567  BANKNL2U  Mr. A. Dinges   98765432             NOTPROVIDED

我一直在修补 PIVOT，但要么它不起作用，要么它没有给出正确的结果。

帮助将不胜感激。

Answer 1

您自己尝试调用 STRING_SPLIT() 证明您使用的是 v2016+。所以你可以调用 JSON 来拯救你:

(感谢 John Cappelletti 制作的模型)

Declare @YourTable table (Bookingnumber varchar(50),paymentrow varchar(max) )
Insert Into @YourTable values
 ('12-3-56789012-3','/TYPE/BOOKING/IBAN/NL12BANK0003456789/BIC/BANKNL2A/NAME/Mr. A. Someguy/CODE/Codenumber 12345678/REF/NOTPROVIDED/LINE/ABCD EFG 234567890 1234 ETC')
,('98-7-65431234-0','/TYPE/BOOKING/IBAN/NL34BANK000123456/BIC/BANKNL2U/NAME/Mr. A. Dinges/CODE/98765432/REF/NOTPROVIDED');

SELECT *
FROM @YourTable t
CROSS APPLY OPENJSON(CONCAT('[{',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        t.paymentrow,'/TYPE/','"Type":"')
                                                     ,'/IBAN/','","IBAN":"')
                                                     ,'/BIC/','","BIC":"')
                                                     ,'/NAME/','","Name":"')
                                                     ,'/CODE/','","Code":"')
                                                     ,'/REF/','","Ref":"')
                                                     ,'/LINE/','","Line":"'),'"}]'))
            WITH([Type] NVARCHAR(100)
                ,[IBAN] NVARCHAR(100)
                ,[BIC] NVARCHAR(100)
                ,[Name] NVARCHAR(250)
                ,[Code] NVARCHAR(100)
                ,[Ref] NVARCHAR(100)
                ,[Line] NVARCHAR(500)) A;

我们使用一些替换将您的字符串转换为这样的 JSON

[{"Type":"BOOKING",
  "IBAN":"NL12BANK0003456789",
  "BIC":"BANKNL2A",
  "Name":"Mr. A. Someguy",
  "Code":"Codenumber 12345678",
  "Ref":"NOTPROVIDED",
  "Line":"ABCD EFG 234567890 1234 ETC"}]

WITH - 子句将隐式执行旋转。

更新如果这是位置安全的

使用固定的输入模式，这更容易

SELECT *
FROM @YourTable t
CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(STUFF(t.paymentrow,1,1,''),'/','","'),'"]]'))
            WITH([Type] NVARCHAR(100) '$[1]'
                ,[IBAN] NVARCHAR(100) '$[3]'
                ,[BIC] NVARCHAR(100) '$[5]'
                ,[Name] NVARCHAR(250) '$[7]'
                ,[Code] NVARCHAR(100) '$[9]'
                ,[Ref] NVARCHAR(100) '$[11]'
                ,[Line] NVARCHAR(500) '$[13]') A;

中间的 JSON 数组如下所示:

["TYPE","BOOKING","IBAN","NL12BANK0003456789","BIC","BANKNL2A","NAME","Mr. A. Someguy","CODE","Codenumber 12345678","REF","NOTPROVIDED","LINE","ABCD EFG 234567890 1234 ETC"]