Sql – How to Optimize this SQL Query

aggregate, sql, sql-server

I have 3 tables:

  • CRSTasks (ID,parentID)
  • CRSTaskReceivers (ID,tskID,receiverID)
  • UserNames (id,name)

…relation between CRSTasks and CRSTaskReceivers one-to-many
between UserNames and CRSTaskReceivers one-to-one

tasks   ID   parent  1     null    10     1      50     1taskReceivers  id      taskID    receiverID  1        1          4(john)  1        10         2(mike)  1        50         3(brand)  

I need result like that:

taskid    Receivers------------------- 1           jone,mike,brand   

ONLY FOR PARENT TASKS IT WILL CONCATE RECEIVERS

Best Solution

SQL Server 2005+:


SELECT t.id AS taskid,       STUFF((SELECT ','+ x.name                FROM (SELECT COALESCE(pu.[ArabicName], aut.Name) AS name                        FROM CRSTaskReceivers tr                         JOIN AD_USER_TBL aut ON aut.id = tr.receiverid                   LEFT JOIN PORTAL_USERS pu ON pu.id = aut.id                       WHERE tr.crstaskid = t.id                         AND tr.receivertype = 1                      UNION                      SELECT agt.name                        FROM CRSTaskReceiver tr                        JOIN AD_GROUP_TBL sgt ON agt.id = tr.receiverid                       WHERE tr.receivertype = 3                         AND tr.crstaskid = t.id) x         FOR XML PATH('')), 1, 1, '')  FROM CRSTasks t

Don't need the function.