0

Although many questions on the same topic I haven't been able to find the right solution. I'm using a piece of software that aggregates IDs to another table as a comma separated value instead of a junction table.

The software exposes three views Department, Team and Task.

For Department

| ID     |  NAME |
|--------|-------|
|  12345 |    IT |
|  25695 |    HR |
|  89685 |   PAY |
|  47896 |   OPS |

Team

| ID     |  NAME |    DEPARTMENT |
|--------|-------|---------------|
|  34252 | TEAM1 |         12345 |
|  54234 | TEAM2 |         12345 |
|  52385 | TEAM1 |          NULL |
|  91231 | TEAM1 |         89685 |

Task

| ID     |  NAME |             Team |
|--------|-------|------------------|
|      1 |    Ta |34252,54234,52385 |
|      2 |    Tb |      52385,91231 |
|      3 |    Tc |             NULL |
|      4 |    Td |            34252 |

I try to achieve the following structure:

| DepID  |   Dep | TeamID |   Team | TaskID |  Task |
|--------|-------|--------|--------|--------|-------|

With a row for every Task, potentially, appearing more than once, if assigned to multiple Teams, and not at all if assigned to no Team or if Team not assigned to a Department yet. Again I only have these three views. What would be the most efficient way to create said structure? I'm on SQLServer 2017.

I've tried starting from the Task table CROSS APPLY STRING_SPLIT and then RIGHT JOIN to Teams as well as Starting from Department LEFT JOIN Teams.

2

1 Answer 1

0

You can use STRING_SPLIT in SQL Server 2016+. Something like this:


SELECT d.ID AS DepID
  , d.NAME AS Dep
  , t.ID AS TeamID
  , t.NAME AS Team
  , tsk.TaskID
  , tsk.Task
FROM
(  
  SELECT tsk.ID AS TaskID
    , tsk.Name AS Task
    , ss.[value] AS TeamID
  FROM Task tsk
  CROSS APPLY STRING_SPLIT(tsk.Team, ',') ss
) tsk
LEFT JOIN Team t ON t.ID = tsk.TeamID
LEFT JOIN Department d ON d.ID = t.DEPARTMENT
DepID Dep TeamID Team TaskID Task
12345 IT 34252 TEAM1 1 Ta
12345 IT 54234 TEAM2 1 Ta
null null 52385 TEAM1 1 Ta
null null 52385 TEAM1 2 Tb
89685 PAY 91231 TEAM1 2 Tb
12345 IT 34252 TEAM1 4 Td

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.