Value Pair Query


DECLARE @TypeMax TABLE
(
MaxCol nvarchar(max)
)

INSERT INTO @TypeMax
VALUES (‘{“ID”:”075405435453.doc”,”Name”:”Scenario1″,”Date”:”2016-12-19 23:01:03″,”Type”:”SL”}’)

, (‘{“ID”:”55453450480003.wav”,”Name”:”Change43″,”Date”:”2016-12-13 21:03:23″,”Type”:”AL”}’)

–SELECT * FROM @TypeMax

–SQL Server 2016 or 2017
SELECT isJSON(MaxCol),
JSON_VALUE(MaxCol, ‘$.ID’) Id,
JSON_VALUE(MaxCol, ‘$.Name’) Name,
JSON_VALUE(MaxCol, ‘$.Date’) [Date],
JSON_VALUE(MaxCol, ‘$.Type’) Type
FROM @TypeMax

–Before JSON functions were introduced to SQL SERVER
—Use XML shredding

;With mycte as (
SELECT Cast(N'<H><r ‘ + Replace(Replace(Replace(Replace(Replace( MaxCol , ‘{“‘,”), ‘}’,”), ‘”,”‘,'” ‘),'”:”‘,’=”‘) + ‘” /></H>’ ,'””‘,'”‘ ) as xml) AS [vals]
FROM @TypeMax
)

,mycte1 as (
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value(‘count(.)’, ‘tinyint’)) rn,
S.a.value(‘@ID’, ‘varchar(50)’) as [ID],
S.a.value(‘@Name’, ‘varchar(50)’) as [Name],
S.a.value(‘@Date’, ‘varchar(50)’) as [Date],
S.a.value(‘@Type’, ‘varchar(50)’) as [Type]

FROM mycte d CROSS APPLY d.[vals].nodes(‘/H/r’) S(a) )

select ID,Name,[Date],[Type] from mycte1
/*
ID Name Date Type
075405435453.doc Scenario1 2016-12-19 23:01:03 SL
55453450480003.wav Change43 2016-12-13 21:03:23 AL
*/

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f229634-d6bb-4096-809b-544895bb0529/sql-help-one-column-spilt-into-multiple-columns?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s