(Classic) Transform Key value pairs into separate columns

[nought]

Hi,

I have a question regarding changing typical JSON key-value pairs into DB columns.

Here's the data schema I'm currently trying to transform:

user channel optstatus

1	Email	true
1	SMS	false
2	Email	true
2	SMS	true
3	Email	false
3	SMS	true

I want to take the value pairs from "channel" and "optstatus" and create a new column in the data schema that would be fed with its values, something like this:

user channel optstatus Email SMS

1	Email	true	true	false
1	SMS	false	true	false
2	Email	true	true	true
2	SMS	true	true	true
3	Email	false	false	true
3	SMS	true	false	true

Or to simplify something like that would be also good:

user Email SMS

1	true	false
2	true	true
3	false	true

I don't wanna change the already-existing structure of the data schema apart from adding new columns. Would I do it in a workflow, query and enrichment, or do some SQL filtering? I would really appreciate your help. Cheers!

[david--garcia]

Can you elaborate more? whats your end goal? also, is your source schema custom? or ootb?

[nought]

My goal is to have columns with unique attributes and their values - currently, I'm getting key-value data through SOAP calls and that JSON-like structure is unnecessarily more complex to work with when it comes to applying rules, filters etc.

The data schema I'm referring to is custom.

[Kishore_Padamata · Accepted solution]

Hello @nought 
Please try below approach:
-> [split] Split by channel

-> [enrichment] set @emailOPT with optstatus (the inbound transition value)

-> [enrichment] set @smsOPT with optstatus (the inbound transition value)
-> [union] chose selection of columns for reconciliation. set user as reconciliation key.

Hope this helps!

[nought]

This is exactly what I was looking for! Thank you, kishorep, this is invaluable!