时间戳

首页 > 解决方案 > 在 SQL 中，如何将逗号分隔的键字符串的列转换为逗号分隔的值字符串

sql - 在 SQL 中，如何将逗号分隔的键字符串的列转换为逗号分隔的值字符串

问题描述

我在 SQL Server 2016 中有一个数据库表（表 A），如下所示：

表A：

TaskID - TaskName  -  AssignedTo
-------------------------------
1        Task 1       1,4
2        Task 2       3
3        Task 3       2,3
4        Task 4       2,4,5

我还有一个 TableB，它是 AssignedTo 的查找表，如下所示：

表B：

AssigneeID - Name
-------------------------------
1            John Smith
2            Janet Wright
3            Tom Morgan
4            Kevin Warren
5            Mike Taylor

我想编写一个查询以生成以下报告/表格：

TaskID - TaskName  -  NameAssignedTo
------------------------------------------------------------
1        Task 1       John Smith,Kevin Warren
2        Task 2       Tom Morgan
3        Task 3       Janet Wright,Tom Morgan
4        Task 4       Janet Wright,Kevin Warren,Mike Taylor

如果可以通过编写 SQL 查询来实现，那就太好了。任何人都可以帮忙吗？非常感谢！

标签： sqlsql-serverreporting

解决方案

下次您需要提供##1-4。并从这个答案中了解它的含义，即一个最小的可重现示例。您将其复制到 SSMS 并在那里启动它。

以下是如何在 SQL Server 2016 中实现它：

  • STRING_SPLIT()分解它，每行一个AssignedTo 。
  • SELECT ... FOR XML ...将其还原为每个任务的一行。

SQL

-- DDL and sample data population, start
DECLARE @tblA TABLE (TaskID INT PRIMARY KEY, TaskName VARCHAR(100), AssignedTo VARCHAR(30));
INSERT INTO @tblA (TaskID, TaskName, AssignedTo) VALUES
(1, 'Task 1', '1,4'),
(2, 'Task 2', '3'),
(3, 'Task 3', '2,3'),
(4, 'Task 4', '2,4,5');

DECLARE @tblB TABLE (AssigneeID INT PRIMARY KEY, [Name] VARCHAR(30));
INSERT INTO @tblB (AssigneeID, [Name]) VALUES
(1, 'John Smith'),
(2, 'Janet Wright'),
(3, 'Tom Morgan'),
(4, 'Kevin Warren'),
(5, 'Mike Taylor')
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH cte AS
(
    SELECT * FROM @tblA
        CROSS APPLY (SELECT value FROM STRING_SPLIT(AssignedTo, @separator)) AS x
        INNER JOIN @tblB AS b ON x.value = b.AssigneeID
)
SELECT p.TaskID, p.TaskName
    , STUFF((SELECT DISTINCT
                     CONCAT(@separator, c.Name)
                     FROM cte AS c
                     WHERE c.TaskID = p.TaskID
                     FOR XML PATH ('')),
             1, 1, '') AS NameAssignedTo
FROM cte AS p
GROUP BY p.TaskID, p.TaskName;

输出

+--------+----------+---------------------------------------+
| TaskID | TaskName |            NameAssignedTo             |
+--------+----------+---------------------------------------+
|      1 | Task 1   | John Smith,Kevin Warren               |
|      2 | Task 2   | Tom Morgan                            |
|      3 | Task 3   | Janet Wright,Tom Morgan               |
|      4 | Task 4   | Janet Wright,Kevin Warren,Mike Taylor |
+--------+----------+---------------------------------------+

推荐阅读