I have following DB structure:
ID Name Value
1 TV1 {"URL": "www.url.com", "Icon": "some_icon"}
2 TV2 {"URL": "www.url.com", "Icon": "some_icon", "Facebook": "Facebook_URL"}
3 TV3 {"URL": "www.url.com", "Icon": "some_icon", "Twitter": "Twitter_URL"}
..........
I am looking for a query with the native functions of SQL Server 2012 to extract the JSON from the column Value
, and dynamically create columns, and I want to do this for different count of columns without hard coding the column names name
, icon
, twitter
, facebook
. So the result I am looking for like:
ID Name URL Icon Facebook Twitter
1 TV1 www.url.com some_icon NULL NULL
2 TV2 www.url.com some_icon Facebook_URL NULL
3 TV3 www.url.com some_icon NULL Twitter_URL
If this is not possible with native SQL Server mechanisms, maybe PostgreSQL can do it, or other RMDBS
PS. My question is not duplicate of Parse JSON in TSQL. I need to find out the way to parse that heterogeneous json in rows