Split multiple values with header included into columns

Question

I have a column containing multiple values separated by a "," also including the headers separated by a ":". The column looks like this:

{"Id":"c7d45d60-cf90-490d-be4c-f04c9349da03","RecordType":20,"CreationTime":"2018-06-12T21:48:30","Operation":"DownloadReport","OrganizationId":"dabd5d90-87c2-44c9-93cd-783e03236e40","UserType":0,"UserKey":"10033FFF85612C0C","Workload":"PowerBI","UserId":"xxxxxx@grundfos.com","ClientIP":"xxx.3.xx.111","UserAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.181 Safari\/537.36","Activity":"DownloadReport","ItemName":"My Salesresponsibility Final 12-06-2018","WorkSpaceName":"My Sales Dashboard testing environment","DatasetName":"My Salesresponsibility Final 12-06-2018","ReportName":"My Salesresponsibility Final 12-06-2018","CapacityId":"65CB790D-16C8-4DA6-BFA2-89854100423E","CapacityName":"Hope","WorkspaceId":"34812eed-830a-43b2-ae18-6c5c1c0c92a1","ObjectId":"My Salesresponsibility Final 12-06-2018","DatasetId":"b6184b73-fea2-4244-83ca-328e89c536af","ReportId":"36e6b6c5-ceeb-4d02-9d54-fb820634e839","IsSuccess":true,"DatapoolRefreshScheduleType":"None"}

Is there a way to split these values and their headers into separate columns? So the first column would be named 'Id' and contain the value 'c7d45d60-cf90-490d-be4c-f04c9349da03' and so forth. I have tried following the most upvoted solution to this similar question, but it doesn't seem to work: How to split a comma-separated value to columns

So I want to go from this:

To something like this:

I have tried to use this code, but it really doesn't work well:

SELECT [AuditData]
      ,Substring(AuditData, 1,Charindex(',', AuditData)-1) as Id
      ,Substring(AuditData, Charindex(',', AuditData)+1, LEN(AuditData)) as  RecordType
      ,Substring(AuditData, Charindex(',', AuditData)+2, LEN(AuditData)) as  CreationTime
  FROM [dbo].[Log]

Inlcude the code you've been trying that doesn't seem to work? — Mazhar, Jul 18 '18 at 10:05
I don't think I can use JSON functions? Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 8 2018 18:18:54 Copyright (C) 2018 Microsoft Corporation — user2776167, Jul 18 '18 at 13:28
JSOn function are available in Azure. Have you tried @shnugo answer below? — Mazhar, Jul 18 '18 at 13:41
Yes, and it seems to work. I just need to figure out how to pivot in the keys in as columns and the values as values in those columns. — user2776167, Jul 18 '18 at 14:02

score 0 · Answer 1 · answered Jul 18 '18 at 10:41

If you fix the data as mentioned in the comments above, then using the Jeff Moden's splitter function you can use this approach

It's better to use separators that will not ever appear in the dataset

;WITH CteSpace AS(
    SELECT *
    FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(REPLACE(@j,'"', ''),'{', ''), '}', '') , ',')
)
SELECT
    [Id]    = LEFT(Item, CHARINDEX(':', Item) - 1),
    [Value] = SUBSTRING(Item, CHARINDEX(':', Item) +1, LEN(Item) - CHARINDEX(':', Item))
FROM CteSpace

provides this output

Id                          Value
Id                          c7d45d60-cf90-490d-be4c-f04c9349da03
RecordType                  20
CreationTime                2018-06-12T21-48-30
Operation                   DownloadReport
OrganizationId              dabd5d90-87c2-44c9-93cd-783e03236e40
UserType                    0
UserKey                     10033FFF85612C0C
Workload                    PowerBI
UserId                      xxxxxx@grundfos.com
ClientIP                    xxx.3.xx.111
UserAgent                   Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML- like Gecko) Chrome\/66.0.3359.181 Safari\/537.36
Activity                    DownloadReport
ItemName                    My Salesresponsibility Final 12-06-2018
WorkSpaceName               My Sales Dashboard testing environment
DatasetName                 My Salesresponsibility Final 12-06-2018
ReportName                  My Salesresponsibility Final 12-06-2018
CapacityId                  65CB790D-16C8-4DA6-BFA2-89854100423E
CapacityName                Hope
WorkspaceId                 34812eed-830a-43b2-ae18-6c5c1c0c92a1
ObjectId                    My Salesresponsibility Final 12-06-2018
DatasetId                   b6184b73-fea2-4244-83ca-328e89c536af
ReportId                    36e6b6c5-ceeb-4d02-9d54-fb820634e839
IsSuccess                   true
DatapoolRefreshScheduleType None

Any string split approach will have to deal with the fact, that a separator, which occurs between qoutes is not a separator. If there is the slightest chance, that one of the values contains a comma within the content (e.g. *ObjectId="My Salesresponsibility, Final 12-06-2018"*), this approach will break... This might pass any test but break in production... — Shnugo, Jul 18 '18 at 11:27
That is true but if the OP doesn't have SQL 2016 then they can't use `OPENJSON` , this doesn't leave ,many other alternatives — Mazhar, Jul 18 '18 at 11:43
True... You would have to run through the characters and watch for opening and closing quotes in order to replace embedded delimiters with another character which has to be re-set after the split. Possible, but heavy... — Shnugo, Jul 18 '18 at 11:44

Shnugo · Accepted Answer · 2018-07-19T10:04:51.003

This is nothing strange, just JSON. Starting with version 2016 you can use FROM OPENJSON() like here:

DECLARE @v VARCHAR(MAX)='{"Id":"c7d45d60-cf90-490d-be4c-f04c9349da03","RecordType":20,"CreationTime":"2018-06-12T21:48:30","Operation":"DownloadReport","OrganizationId":"dabd5d90-87c2-44c9-93cd-783e03236e40","UserType":0,"UserKey":"10033FFF85612C0C","Workload":"PowerBI","UserId":"xxxxxx@grundfos.com","ClientIP":"xxx.3.xx.111","UserAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.181 Safari\/537.36","Activity":"DownloadReport","ItemName":"My Salesresponsibility Final 12-06-2018","WorkSpaceName":"My Sales Dashboard testing environment","DatasetName":"My Salesresponsibility Final 12-06-2018","ReportName":"My Salesresponsibility Final 12-06-2018","CapacityId":"65CB790D-16C8-4DA6-BFA2-89854100423E","CapacityName":"Hope","WorkspaceId":"34812eed-830a-43b2-ae18-6c5c1c0c92a1","ObjectId":"My Salesresponsibility Final 12-06-2018","DatasetId":"b6184b73-fea2-4244-83ca-328e89c536af","ReportId":"36e6b6c5-ceeb-4d02-9d54-fb820634e839","IsSuccess":true,"DatapoolRefreshScheduleType":"None"}';
SELECT *
FROM OPENJSON(@v);

The result

key            value                                 type
Id             c7d45d60-cf90-490d-be4c-f04c9349da03  1
RecordType     20                                    2
CreationTime   2018-06-12T21:48:30                   1
[... more rows ...]

UPDATE get this as PIVOT list

There are two approaches:

DECLARE @v VARCHAR(MAX)='{"Id":"c7d45d60-cf90-490d-be4c-f04c9349da03","RecordType":20,"CreationTime":"2018-06-12T21:48:30","Operation":"DownloadReport","OrganizationId":"dabd5d90-87c2-44c9-93cd-783e03236e40","UserType":0,"UserKey":"10033FFF85612C0C","Workload":"PowerBI","UserId":"xxxxxx@grundfos.com","ClientIP":"xxx.3.xx.111","UserAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.181 Safari\/537.36","Activity":"DownloadReport","ItemName":"My Salesresponsibility Final 12-06-2018","WorkSpaceName":"My Sales Dashboard testing environment","DatasetName":"My Salesresponsibility Final 12-06-2018","ReportName":"My Salesresponsibility Final 12-06-2018","CapacityId":"65CB790D-16C8-4DA6-BFA2-89854100423E","CapacityName":"Hope","WorkspaceId":"34812eed-830a-43b2-ae18-6c5c1c0c92a1","ObjectId":"My Salesresponsibility Final 12-06-2018","DatasetId":"b6184b73-fea2-4244-83ca-328e89c536af","ReportId":"36e6b6c5-ceeb-4d02-9d54-fb820634e839","IsSuccess":true,"DatapoolRefreshScheduleType":"None"}';

--PIVOT

SELECT p.*
FROM
(
    SELECT [value],[key]
    FROM OPENJSON(@v)
) t
PIVOT
(
    MAX(t.[value]) FOR t.[key] in(Id,RecordType,CreationTime)
) p;

--JSON_VALUE

SELECT JSON_VALUE(@v,'$.Id') AS Id
      ,JSON_VALUE(@v,'$.RecordType') AS RecordType
      ,JSON_VALUE(@v,'$.CreationTime') AS CreationTime

Try it out...

UPDATE 2: Get this from a table

This is simple.

DECLARE @tbl TABLE(ID INT IDENTITY, yourJSON NVARCHAR(MAX));
INSERT INTO @tbl VALUES('{"Id":"blah1","RecordType":10]}')
                      ,('{"Id":"blah2","RecordType":20]}')
                      ,('{"Id":"blah3","RecordType":30]}');
SELECT t.ID 
      ,JSON_VALUE(t.yourJSON,'$.Id') AS JsonId
      ,JSON_VALUE(t.yourJSON,'$.RecordType') AS RecordType
FROM @tbl t;

Hint

For your next question: Please do not paste pictures. Try to add a reduced stand-alone running sample (so called MCVE) as I do it above. Oterwise people have to type this in for you.

This solution seems nice and simple, but how do I get the values in separate columns instead of row values in the key column? — user2776167, Jul 18 '18 at 13:35
@user2776167 If you know all Keys in advance you can use simple pivot. — Shnugo, Jul 18 '18 at 13:41
I do and they shouldn't change over time. I'll look into pivoting the keys and values into columns. Any hints on how to do that? @Shnugo — user2776167, Jul 19 '18 at 07:34
That also works perfectly! Many thanks for writing such clear code! — user2776167, Jul 19 '18 at 08:39
Now I just need to figure out how to do it for each row in the table instead of just one variable. — user2776167, Jul 19 '18 at 09:30
Many thanks again! You have been a great help and I found the answer based on your code. Also thanks for pointing out the MCVE etiquette. I can't give you any stars due to my low rank, but you can get an ASCII high-five ヘ( ^o^)ノ＼(^_^ ) — user2776167, Jul 20 '18 at 06:57

score 0 · Answer 3 · answered Jul 18 '18 at 11:53

If you don't have SQL 2016 and want to use JSON string then you can use Phil Factor's ParseJSON function

Then, as @Shnugo has mentioned, there's no need to worry about separators appearing in your dataset and breaking your code

DECLARE @j NVARCHAR(MAX) = '{"Id":"c7d45d60-cf90-490d-be4c-f04c9349da03","RecordType":20,"CreationTime":"2018-06-12T21-48-30","Operation":"DownloadReport","OrganizationId":"dabd5d90-87c2-44c9-93cd-783e03236e40","UserType":0,"UserKey":"10033FFF85612C0C","Workload":"PowerBI","UserId":"xxxxxx@grundfos.com","ClientIP":"xxx.3.xx.111","UserAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML- like Gecko) Chrome\/66.0.3359.181 Safari\/537.36","Activity":"DownloadReport","ItemName":"My Salesresponsibility Final 12-06-2018","WorkSpaceName":"My Sales Dashboard testing environment","DatasetName":"My Salesresponsibility Final 12-06-2018","ReportName":"My Salesresponsibility Final 12-06-2018","CapacityId":"65CB790D-16C8-4DA6-BFA2-89854100423E","CapacityName":"Hope","WorkspaceId":"34812eed-830a-43b2-ae18-6c5c1c0c92a1","ObjectId":"My Salesresponsibility Final 12-06-2018","DatasetId":"b6184b73-fea2-4244-83ca-328e89c536af","ReportId":"36e6b6c5-ceeb-4d02-9d54-fb820634e839","IsSuccess":true,"DatapoolRefreshScheduleType":"None"}'


SELECT name, stringvalue
FROM dbo.parseJSON(@j)

Output

name            stringvalue
Id              c7d45d60-cf90-490d-be4c-f04c9349da03
RecordType      20
CreationTime    2018-06-12T21-48-30
Operation       DownloadReport
OrganizationId  dabd5d90-87c2-44c9-93cd-783e03236e40
UserType        0
UserKey         10033FFF85612C0C

score 0 · Answer 4 · answered Jul 20 '18 at 06:53

In a very roundabout way, I got the answer I needed. I made a new view containing the JSON from the column in the following way:

SELECT
        JSON_VALUE(AuditData,'$.Id') AS Id
        ,JSON_VALUE(AuditData,'$.RecordType') AS RecordType
        ,JSON_VALUE(AuditData,'$.CreationTime') AS CreationTime
        ,JSON_VALUE(AuditData,'$.Operation') AS Operation
        ,JSON_VALUE(AuditData,'$.OrganizationId') AS OrganizationId
        ,JSON_VALUE(AuditData,'$.UserType') AS UserType
        ,JSON_VALUE(AuditData,'$.UserKey') AS UserKey
        ,JSON_VALUE(AuditData,'$.Workload') AS Workload
        ,JSON_VALUE(AuditData,'$.UserId') AS UserId
        ,JSON_VALUE(AuditData,'$.ClientIP') AS ClientIP
        ,JSON_VALUE(AuditData,'$.UserAgent') AS UserAgent
        ,JSON_VALUE(AuditData,'$.Activity') AS Activity
        ,JSON_VALUE(AuditData,'$.ItemName') AS ItemName
        ,JSON_VALUE(AuditData,'$.WorkSpaceName') AS WorkSpaceName
        ,JSON_VALUE(AuditData,'$.DatasetName') AS DatasetName
        ,JSON_VALUE(AuditData,'$.ReportName') AS ReportName
        ,JSON_VALUE(AuditData,'$.CapacityId') AS CapacityId
        ,JSON_VALUE(AuditData,'$.CapacityName') AS CapacityName
        ,JSON_VALUE(AuditData,'$.WorkspaceId') AS WorkspaceId
        ,JSON_VALUE(AuditData,'$.ObjectId') AS ObjectId
        ,JSON_VALUE(AuditData,'$.DatasetId') AS DatasetId
        ,JSON_VALUE(AuditData,'$.ReportId') AS ReportId
        ,JSON_VALUE(AuditData,'$.IsSuccess') AS IsSuccess
        ,JSON_VALUE(AuditData,'$.DatapoolRefreshScheduleType') AS DatapoolRefreshScheduleType

FROM [dbo].[Audit_Log];

Many thanks to @Shnugo for helping out a newbie like me to Stackoverflow.

Split multiple values with header included into columns

4 Answers4

UPDATE get this as PIVOT list

UPDATE 2: Get this from a table

Hint