'How to take dictionary and return a table with a row for each

I have a table like this, status_change is a dictionary, it has multiple status,
enter image description here

How can i return a table: the dictionary key becomes the column header enter image description here

Appreciate your help!

sql


Solution 1:[1]

this is an example to read json to table and pivot to able to add to temp table

DECLARE @json nvarchar(500) = '{"Not Started":"2022-02-25","Started":"2022-02-22","End":"2022-02-24"}'
--SET @json = '{"Not Started":"2022-02-25","Started":"2022-02-22"}'

SELECT resTable.[End],
       resTable.[Started],
       resTable.[Not Started] 
FROM
(SELECT [Key] AS col, Value AS val FROM OPENJSON(@json)) AS j
PIVOT(
    MAX(j.val)
    FOR col IN ([Not Started], [Started], [End])
) AS resTable

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Power Mouse

Related Questions