looking for some help on how to transform some data.

Field_Name has 50 different possible values, and I'm interested in presenting a subset of those values as column headers in a query result. Example desired result shown below:

I've used pivots for this type of query in the paste, but always when I'm aggregating results. Any guidance / recommendations?

CodePudding user response：

Based on the data shared, assuming entity_id and entity_name will be same in group, one approach can be as follows -

with data_cte (Entity_ID,Entity_Name,Field_Name,Field_Value) as
(
select * from values
(1,'Entity_A','results.Address','123 ABC Way'),
(1,'Entity_A','results.City','Wonkaland')
), get_col_cte as
(
select Entity_ID,
Entity_Name,
case
when field_name='results.Address' then field_value end results_address,
case
when field_name='results.City' then field_value end results_city
from data_cte
)
select Entity_ID,
Entity_Name,
listagg(results_address) results_address,
listagg(results_city) results_city
from get_col_cte
group by
Entity_ID,
Entity_Name;

And usual PIVOT approach -

with data_cte (Entity_ID,Entity_Name,Field_Name,Field_Value) as
(
select * from values
(1,'Entity_A','results.Address','123 ABC Way'),
(1,'Entity_A','results.City','Wonkaland')
)
select * from data_cte
pivot (max(Field_Value) 
for field_name in ('results.Address','results.City')
)

CodePudding user response：

Convert you data into JSON VARIANT. Then you can SELECT VAR_COLUMN.field_name as needed. You can use LISTAGG and PARSE_JSON to create the VARIANT Object.