Instead off one line with the json entries nested into one single field i would like to Normalize the tabel into several lines, one for each SKU.
Any ideas?
Page 1 / 1
If your field Inventory_Entries has a maximum number of entries per Inventory_id, I would suggest:
1. CROSS APPLY or UNPIVOT the Inventory_Entries field into a temp table or a custom table
2. LEFT JOIN that new temp /custom table to your original table and store the output of that join in another custom table
Since I can't tell what the PK is of your table, I can only suggest that it's best to unpivot or cross apply round your PK and perform your join on that. I assumed here that it's Inventory_id.
Kind Regards,
Wim
Hi
Apologies for the late response. I recently had another question regarding this. For example, the below data was provided in JSON format
select [CurrencyCode] , JSON_QUERY([JSONField],'$.bricks') as bricks , JSON_QUERY([JSONField],'$.mortar') as mortar , JSON_QUERY([JSONField],'$.size') as size , JSON_QUERY([JSONField],'$.texture_Size') as texture_Size , JSON_QUERY([JSONField],'$.texture_Type') as texture_Type , JSON_value([JSONField],'$.seed') as seed , JSON_value([JSONField],'$.tileable') as tileable , JSON_value([JSONField],'$.result_Type') as result_Type
FROM [AdventureWorks].[Sales].[Currency]
Second custom view:
CREATE VIEW [dbo].[CurrencyJSON2] as SELECT [CurrencyCode] ,JSON_VALUE(replace(replace([bricks],'[',''),']',''),'$.collection_Id') as collection_Id ,JSON_VALUE(replace(replace([bricks],'[',''),']',''),'$.usage_Percentage') as usage_Percentage ,JSON_VALUE([mortar],'$.color') as color ,JSON_VALUE([mortar],'$.mortar_Id') as mortar_Id ,JSON_VALUE([mortar],'$.bed_Size') as bed_Size ,JSON_VALUE([mortar],'$.perpend_Size') as perpend_Size ,JSON_VALUE([size],'$.x') as size_x ,JSON_VALUE([size],'$.y') as size_y ,JSON_VALUE([texture_Size],'$.x') as texture_size_x ,JSON_VALUE([texture_Size],'$.y') as texture_size_y ,[texture_Type] ,[seed] ,[tileable] ,[result_Type] FROM [AdventureWorks].[dbo].[CurrencyJSON]
I then created a table, added the fields and then added a table insert from my second view
A preview of the data
You should also be able to do the same with custom field transformations instead of views. For example: