Parsing out a single column into multiple

Question

I have a column with a "Payload" of data. It represents a dynamic field where an application pulls all of the data from a source (API, stored procedure, etc) into one column in one table. Each payload can be a different set of columns, so I cannot land the information into a table. But I need my team to be able to pull out the information. The delimiters are static. Example: Source table looks like this

ID    Payload
123   {"Transaction":"123456","Transaction2":"789123"}
124   {"Transaction":"123457","Transaction2":"789124"}

I would like the final output to be:

ID     Transaction   Transaction2
123    123456        789123
124    123457        789124

I have a split function that I can pass through twice to get me the following:

ID   SplitID      Split               SplitID2    Split 2
123  1            transaction:123456  1           transaction
123  1            transaction:123456  2           123456
123  2            transaction2:789123 1           transaction2
123  2            transaction2:789123 2           789123
124  1...            

So now I need to flatten this without using dynamic SQL... OR putting this in a place where a team of 20 can consume and use on a regular basis with NO persisting tables, etc...

Edit: We currently have SQL 2012. We are a few months out from 2017. Sorry for the lack of documenting.

Answer 1

If that document would be slightly more polished for a JSON and you run SQL Server 2016+..:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
      {"ID" : "123", "Payload" : {"Transaction":"123456","Transaction2":"789123"}},
      {"ID" : 124, "Payload" : {"Transaction":"123457","Transaction2":"789124"}}
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int '$.ID',  
        Transaction1 int '$.Payload.Transaction',
        Transaction2 int '$.Payload.Transaction2'

        )

Answer 2

If you can try RegexAssembly

    declare  @valcheck table
(
    id int identity(1,1) primary key,
    payload varchar(100)
)

insert into @valcheck(payload) 
values('{"Transaction":"123456","Transaction2":"789123"}'),
      ('{"Transaction":"123457","Transaction2":"789124"}')


select id, [1] as 'Transaction1',[2] as 'Transaction2' from @valcheck a
cross apply(
            select [1],[2] from
                (select dbo.RegExMatch('(?<=:")\d+',Match,1) as match, 
                        row_number()over(order by (select 0)) as rn 
                        from dbo.RegExSplit(',',a.payload,1)) as D
        pivot
           (max(match) for rn in ([1],[2]) ) as pivoteed
)c

Answer 3

I'm getting the sense that you are not on 2016+

SQL Server is declarative by design, so getting variable columns without going dynamic is not possible.

If you have a maximumn number of columns, consider the following:

Example

Select A.ID
      ,C.*
 From  YourTable A
 Cross Apply (Select CleanString = replace(replace(replace(replace(Payload,'{',''),',',':'),'"',''),'}','') ) B
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[10]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[12]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[14]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[16]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[18]','varchar(max)')))
                From  (Select Cast('<x>' + replace(CleanString,':','</x><x>')+'</x>' as xml) as xDim) as A 
             ) C

Returns

ID  Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9  
123 123456  789123  NULL    NULL    NULL    NULL    NULL    NULL    NULL
124 123457  789124  NULL    NULL    NULL    NULL    NULL    NULL    NULL

Answer 4

If you have SQL Server 2016, you can use json_value:

select  id
,       json_value(payload, '$.Transaction')
,       json_value(payload, '$.Transaction2')
from    t1

Example at SQL Fiddle.

For earlier versions of SQL Server, you'd need a CLR UDF, like JSON Select.

Answer 5

If you DON'T have SQL Server 2016 or above, then the answer is a bit more complicated. What I would do is stop after the first split (don't split the name and the value into separate rows), so your table looks like:

ID   SplitID      Split               
123  1            transaction:123456  
123  2            transaction2:789123 
124  1...            

...one transaction/row, one row/transaction. Assuming that the split did remove the quotes, as it appears, then something like the following should work (untested):

select  id
    , Transaction
    , Transaction2
FROM
    (SELECT LEFT(Split, CHARINDEX(':', Split)-1) AS FieldName
         SUBSTRING(Split, CHARINDEX(':', Split)+1,999) AS FieldValue
         FROM udf.splittable(t1)) as sourcetable
PIVOT
(
    MIN(FieldValue)
    FOR FieldName IN ([Transaction], [Transaction2])
) AS PivotTable;

...but yeah, if you have 2016 or higher, go with the JSON route mentioned in other answers. :)