将单个列解析为多个 [英] Parsing out a single column into multiple

问题描述

我有一列包含有效载荷"数据的列.它代表一个动态字段，在该字段中，应用程序将来自源(API，存储过程等)的所有数据提取到一张表的一列中.每个有效负载可以是一组不同的列，因此我无法将信息放入表中.但是我需要我的团队能够提取信息.分隔符是静态的. 示例:源表如下所示:

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"}

我希望最终输出为:

ID     Transaction   Transaction2
123    123456        789123
124    123457        789124

我有一个split函数，我可以通过两次以获取以下内容:

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...            

所以现在我需要在不使用动态SQL的情况下对其进行扁平化处理……或者将其放置在一个20人的团队可以定期使用和使用而没有任何持久化表的地方……

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...

我们目前有SQL2012.与2017年相比，还有几个月的时间.很抱歉缺少文档.

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

推荐答案

我感觉到您不在2016年以上

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

SQL Server在设计上是声明性的，因此不可能在不动态的情况下获取变量列.

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:

示例

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

返回

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

这篇关于将单个列解析为多个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！