将单个列解析为多个 [英] 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屋!