Pivot the result of SPLIT_STRING?

Question

I have a table with a column with strings like these:

/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

And I want to look up individual elements in these strings without having to write unreadable code with many CHARINDEX-es and SUBSTRINGS. So I found the SPLIT_STRING function.

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

It's great, but now I get these values in rows:

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

So as a final step, I would to pivot the value column so that the elements in the original string appear as neat columns, like this:

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

I've been tinkering with PIVOT, but either it doesn't work, or it doesn't give the right results.

Help would be greatly appreciated.

user_1570000 · Answer

IF the columns are not dynamic, perhaps a little XML

Example

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 A.Bookingnumber
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Type = xDim.value('/x[3]','varchar(max)')
                      ,IBan = xDim.value('/x[5]','varchar(max)')
                      ,BIC  = xDim.value('/x[7]','varchar(max)')
                      ,Name = xDim.value('/x[9]','varchar(max)')
                      ,Code = xDim.value('/x[11]','varchar(max)')
                      ,Ref  = xDim.value('/x[13]','varchar(max)')
                From  ( values (cast('' + replace(paymentrow,'/','')+'' as xml))) as A(xDim)
             ) B

Returns

user_5089204 · Answer

Your own attempt calling STRING_SPLIT() proofs, that you are using v2016+. So you can call JSON to your rescue:

(Credits to John Cappelletti for the mockup)

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;

We use some replacements to transform your string to such a JSON

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

The WITH-clause will do the pivoting implicitly.

UPDATE If this is position-safe

With a fixed input pattern, this is easier

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;

The intermediate JSON-array looks like this:

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

user_50552 · Answer

SELECT  contract
,       x.value('/M[3]', 'nvarchar(max)') as IBAN
,       x.value('/M[5]', 'nvarchar(max)') as BIC
,       x.value('/M[7]', 'nvarchar(max)') as NAME
FROM    (
        SELECT  contract
        ,       CAST('' + REPLACE(paymentrow, '/', '') + '' AS xml) AS x
        FROM    payments
        ) sub

Example at dbfiddle.co.uk

Pivot the result of SPLIT_STRING?

3 Answers

UPDATE If this is position-safe