CODEWITHSUNDEEP
sqlsql-serverreporting
Denis
Denis

Reputation: 75

In SQL, how to convert a column of a comma separated key string to a comma separated value string

I have a database table (table A) looks like this, in SQL Server 2016:

TableA:

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

I also have a TableB which is a lookup table for AssignedTo which looks like this:

TableB:

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

I want to write a query to be able to genrate the following report/table:

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

If it can be achieved by writing a SQL query that would be great. Anyone can help? Thank you very much!

Upvotes: 0

Views: 1073

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The schema you have is fundamentally broken. You really want an additional table here to express the TaskAssignments like this:

TaskID Assignee
1 1
1 4
2 3
3 2
3 3
4 2
4 4
4 5

You can find other solutions that will give you the results for this query, but this will make it much easier to write, it will perform multiple orders of magnitude better, and it's also better for answering questions (queries) you haven't even thought to ask yet.

General rule of thumb here: humans prefer fewer, wider tables, but computers do better with more, narrower tables. So we design the database storage to appease the computer, and write reports to show combined data for the humans.

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

Next time you would need to provide ##1-4. And learn from this answer what it means, i.e. a minimal reproducible example. You copy it to SSMS and launch it there.

Here is how to implement it in SQL Server 2016:

  • STRING_SPLIT() to break it down, one AssignedTo per row.
  • SELECT ... FOR XML ... to revert it back to one row for each task.

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;

Output

+--------+----------+---------------------------------------+
| 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 |
+--------+----------+---------------------------------------+

Upvotes: 2

Related Questions