文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >将一列拆分为多列

问将一列拆分为多列
EN

Stack Overflow用户
提问于 2018-09-04 17:58:08
回答 3查看 299关注 0票数 1

我在做一个权力比审计日志报告文件。该文件包含一个列'AuditDate‘，其中包含多个列。我需要使用sql将该列拆分为多个列。

该列的值如下所示

代码语言：javascript
复制
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""}"

基本上我需要把这个专栏分成

代码语言：javascript
复制
id     RecordType      CreationTime    Operaration     OrganizationID  UserType
------------------------------------------------------------------------------
44de2468    20     2018-08-03T12:30:34   ViewReport     779558               0

有人能帮上这个sql查询吗？

sql
json
sql-server
tsql
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2018-09-04 21:19:43

看起来您正在处理的是一个格式错误的JSON列。那些双引号太麻烦了。

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

首先，设置数据(使用您在另一个问题副本(将列值拆分为多列)中提供的数据)：

代码语言：javascript
复制
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。

代码语言：javascript
复制
UPDATE @t
SET AuditDate = REPLACE(AuditDate,'""','"');

验证JSON看起来不错。

代码语言：javascript
复制
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()提取您感兴趣的部分。

代码语言：javascript
复制
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   |
+------------+----------+------+--------------+
票数 2
EN

Stack Overflow用户

发布于 2018-09-04 18:24:58

这很简单，您所需要的只是一个字符串“拆分器”(AKA令牌程序)。如果您使用的是SQL 2016+，则可以使用STRING_SPLIT；如果您使用的是2016年前的系统，则可以在2005+上使用DelimitedSplit8K或在2012+上使用铅。解决方案将如下所示：

代码语言：javascript
复制
DECLARE @AuditDate VARCHAR(8000) = 
'"{""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""}"'

SELECT 
  Id             = MAX(CASE split.attrib WHEN 'ID'             THEN split.val END),
  RecordType     = MAX(CASE split.attrib WHEN 'RecordType'     THEN split.val END),
  CreationTime   = MAX(CASE split.attrib WHEN 'CreationTime'   THEN split.val END),
  Operation      = MAX(CASE split.attrib WHEN 'Operation'      THEN split.val END),
  OrganizationId = MAX(CASE split.attrib WHEN 'OrganizationId' THEN split.val END),
  UserType       = MAX(CASE split.attrib WHEN 'UserType'       THEN split.val END)
FROM
(
  SELECT      attrib = REPLACE(REPLACE(SUBSTRING(split.value, 1, mid.point-1),'{',''),'"',''),
              val    = REPLACE(REPLACE(SUBSTRING(split.value, mid.point+1, 8000),'{',''),'"','')
  FROM        STRING_SPLIT(@AuditDate,',') AS split
  CROSS APPLY (VALUES(CHARINDEX(':', split.value))) AS mid(point)
  WHERE       REPLACE(REPLACE(SUBSTRING(split.value, 1, mid.point-1),'{',''),'"','') IN
                ('id','RecordType','CreationTime','Operation','OrganizationID','UserType')
) AS split;

结果：

代码语言：javascript
复制
Id         RecordType  CreationTime          Operation   OrganizationId  UserType
---------- ----------- --------------------- ----------- --------------- ---------
44de2468   20          2018-08-03T12:30:34   ViewReport  779558          0
票数 2
EN

Stack Overflow用户

发布于 2018-09-05 11:56:56

对于Server 2016，这是相当简单的。有相当多的JSON支持。你唯一的问题是，你的字符串不正确。很明显，有一个引擎加倍所有的内部引号(一种转义技术)。

如果这是在您的控制之下，您应该尝试将您的列的格式更改为正确的JSON。更好的是，让编写应用程序以正确的JSON格式提供这些审核。至少您可以添加第二列并使用触发器来保持同步。作为最后的手段，您可以使用REPLACE修复字符串：

代码语言：javascript
复制
REPLACE(REPLACE(REPLACE(@YourString,'"{','{'),'}"','}'),'""','"');

很多行这可能需要一段时间..。这就是为什么最好将格式保持在适当的JSON中。

只是为了展示这些原则：

代码语言：javascript
复制
DECLARE @YourString NVARCHAR(MAX)=N'"{""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""}"';

SET @YourString = REPLACE(REPLACE(REPLACE(@YourString,'"{','{'),'}"','}'),'""','"');

您的字符串现在将如下所示：

代码语言：javascript
复制
{"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"}

此查询将返回所有列作为驱动列表：

代码语言：javascript
复制
SELECT * 
FROM OPENJSON(@YourString);

结果返回一个带有类型提示的列表(而实际的“值”类型是nvarchar)：

代码语言：javascript
复制
+-----------------------------+-------------------------------+------+
| key                         | value                         | type |
+-----------------------------+-------------------------------+------+
| Id                          | 44de2468                      | 1    |
+-----------------------------+-------------------------------+------+
| RecordType                  | 20                            | 2    |
+-----------------------------+-------------------------------+------+
| CreationTime                | 2018-08-03T12:30:34           | 1    |
+-----------------------------+-------------------------------+------+
| Operation                   | ViewReport                    | 1    |
+-----------------------------+-------------------------------+------+
| OrganizationId              | 779558                        | 1    |
+-----------------------------+-------------------------------+------+
| UserType                    | 0                             | 2    |
+-----------------------------+-------------------------------+------+
| UserKey                     | FFFA3DA                       | 1    |
+-----------------------------+-------------------------------+------+
| Workload                    | PowerBI                       | 1    |
+-----------------------------+-------------------------------+------+
| UserId                      | john@abc.com                  | 1    |
+-----------------------------+-------------------------------+------+
| ClientIP                    | 9.5.3.26                      | 1    |
+-----------------------------+-------------------------------+------+
| UserAgent                   | Mozilla/5.0 (Windows NT 10.0; | 1    |
+-----------------------------+-------------------------------+------+
| Activity                    | ViewReport                    | 1    |
+-----------------------------+-------------------------------+------+
| ItemName                    | Sales                         | 1    |
+-----------------------------+-------------------------------+------+
| WorkSpaceName               | TeamITO                       | 1    |
+-----------------------------+-------------------------------+------+
| DatasetName                 | Sales1                        | 1    |
+-----------------------------+-------------------------------+------+
| ReportName                  | Sales1                        | 1    |
+-----------------------------+-------------------------------+------+
| WorkspaceId                 | e8eaa0ca                      | 1    |
+-----------------------------+-------------------------------+------+
| ObjectId                    | Sales1                        | 1    |
+-----------------------------+-------------------------------+------+
| DatasetId                   | 4c5d-ad45-eb6546              | 1    |
+-----------------------------+-------------------------------+------+
| ReportId                    | 4cb0-99ad-de41b5160c47        | 1    |
+-----------------------------+-------------------------------+------+
| IsSuccess                   | true                          | 3    |
+-----------------------------+-------------------------------+------+
| DatapoolRefreshScheduleType | None                          | 1    |
+-----------------------------+-------------------------------+------+
| DatapoolType                | Undefined                     | 1    |
+-----------------------------+-------------------------------+------+

更好的是，您可以添加一个WITH子句，如下所示：

代码语言：javascript
复制
SELECT * 
FROM OPENJSON(@YourString)
WITH 
(   
    Id             varchar(200)  '$.Id',  
    RecordType     int           '$.RecordType',  
    CreationTime   datetime      '$.CreationTime'
    --Add all your known columns here...
)

这样你就可以把你的值输入到并排的位置。

代码语言：javascript
复制
+----------+------------+-------------------------+
| Id       | RecordType | CreationTime            |
+----------+------------+-------------------------+
| 44de2468 | 20         | 2018-08-03 12:30:34.000 |
+----------+------------+-------------------------+
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/52171710

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档