时间戳

首页 > 解决方案 > 将一列拆分为多列

sql - 将一列拆分为多列

问题描述

我正在处理一个 power bi 审计日志报告文件。该文件包含一列“AuditDate”，其中包含多个列。我需要使用 sql 将该列拆分为多个列。

该列具有这样的值

AuditDate
------------
"{""Id"":""44de2468"",""RecordType"":20,""CreationTime"":""2018-08-03T12:30:34"",""Operation"":""ViewReport"",""OrganizationId"":""779558"",""UserType"":0,""UserKey"":""FFFA3DA"",""Workload"":""PowerBI"",""UserId"":""john@abc.com"",""ClientIP"":""9.5.3.26"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0;"",""Activity"":""ViewReport"",""ItemName"":""Sales"",""WorkSpaceName"":""TeamITO"",""DatasetName"":""Sales1"",""ReportName"":""Sales1"",""WorkspaceId"":""e8eaa0ca"",""ObjectId"":""Sales1"",""DatasetId"":""4c5d-ad45-eb6546"",""ReportId"":""4cb0-99ad-de41b5160c47"",""IsSuccess"":true,""DatapoolRefreshScheduleType"":""None"",""DatapoolType"":""Undefined""}"

基本上我需要将此列拆分为

id     RecordType      CreationTime    Operaration     OrganizationID  UserType
------------------------------------------------------------------------------
44de2468    20     2018-08-03T12:30:34   ViewReport     779558               0

任何人都可以为此提供sql查询吗？

标签： sqljsonsql-servertsql

解决方案

看起来您正在处理格式错误的 JSON 列。那些双双引号很麻烦。

但是，如果您可以清理格式，则可以在查询中使用 JSON 函数。

首先，设置数据（使用您在此问题的其他副本中提供的数据，（将列值拆分为多个列）：

DECLARE @t TABLE 
(
  RecordType NVARCHAR(20)
  ,AuditDate NVARCHAR(MAX)
);
INSERT @t
  (
    RecordType
    ,AuditDate
  )
VALUES
  ('View', '{""Id"":""44de2468"",""Type"":20,""CreationDate"":""2018-08-23""}')
 ,('Edit', '{""Id"":""44de2467"",""Type"":40,""CreationDate"":""2018-08-24""}')
 ,('Print', '{""Id"":""44de2768"",""Type"":60,""CreationDate"":""2018-05-06""}')
 ,('Delete', '{""Id"":""44de2488"",""Type"":30,""CreationDate"":""2018-07-20""}');

现在，通过用单双引号替换双双引号来清理格式错误的 JSON。

UPDATE @t
SET AuditDate = REPLACE(AuditDate,'""','"');

验证 JSON 看起来不错。

SELECT * FROM @t

--Results:
+------------+---------------------------------------------------------+
| RecordType |                        AuditDate                        |
+------------+---------------------------------------------------------+
| View       | {"Id":"44de2468","Type":20,"CreationDate":"2018-08-23"} |
| Edit       | {"Id":"44de2467","Type":40,"CreationDate":"2018-08-24"} |
| Print      | {"Id":"44de2768","Type":60,"CreationDate":"2018-05-06"} |
| Delete     | {"Id":"44de2488","Type":30,"CreationDate":"2018-07-20"} |
+------------+---------------------------------------------------------+

然后JSON_VALUE()用来提取你感兴趣的部分。

SELECT 
    RecordType
  , JSON_VALUE(AuditDate, '$.Id') AS [Id]
  , JSON_VALUE(AuditDate, '$.Type') AS [Type]
  , JSON_VALUE(AuditDate, '$.CreationDate') AS CreationDate
FROM @t

--Results
+------------+----------+------+--------------+
| RecordType |    Id    | Type | CreationDate |
+------------+----------+------+--------------+
| View       | 44de2468 |   20 | 2018-08-23   |
| Edit       | 44de2467 |   40 | 2018-08-24   |
| Print      | 44de2768 |   60 | 2018-05-06   |
| Delete     | 44de2488 |   30 | 2018-07-20   |
+------------+----------+------+--------------+

推荐阅读