SQL - combine multiple rows of data into one row and columns
I want to create a SQL Server query that will combine resources with the same Task into a single row / record row using three tables. My SQL Server query below seems to be malfunctioning and taking a very long time and then errors. Thank!
Task table
TaskUID
TaskName
Assignment table
TaskUID
ResourceUID
Resource table
ResourceUID
ResourceName
Before
**Task Name Resource Name**
Weapon Launch Amy
Weapon Launch Sam
Weapon Launch Marisa
Weapon Launch Katy
Weapon Launch John
Sweating Tears Marisa
Sweating Tears Joe
Sweating Tears Katy
Sweating Tears Michael
Ramp Diver Joe
Ramp Diver Michael
After
**Task Name Resource Name**
Weapon Launch Amy; Sam; Marisa; Katy; John
Sweating Tears Marisa; Joe; Katy; Michael
Ramp Diver Michael; Joe
Query
SELECT T.TaskName,
STUFF(( SELECT ', ' + R.RESOURCENAME
FROM
[Resource Table] R
LEFT JOIN [Assignment Table] A ON R.ResourceUID=A.ResourceUID
WHERE
A.TASKUID=T.TaskUID
Group by R.RESOURCENAME
FOR XML PATH('')), 1, 1,'') Resources
FROM [Task Table] T
INNER JOIN [Assignment Table] A ON T.TASKUID=A.TASKUID
+3
source to share
2 answers
Try:
SELECT T.TaskName, Resources
FROM TaskTable T
CROSS APPLY (
SELECT
STUFF(( SELECT ', ' + R.RESOURCENAME
FROM [ResourceTable] R
INNER JOIN [AssignmentTable] A ON R.ResourceUID=A.ResourceUID
WHERE A.TASKUID = T.TASKUID
Group by R.RESOURCENAME
FOR XML PATH('')), 1, 1,'') Resources
) N(Resources)
0
source to share
Thanks to Nizam, I realized that I should use tables in the database instead of views, since I do not have rights to create indexes. Built-in indexes allow for faster queries.
SELECT DISTINCT
T.TASK_NAME,
STUFF((SELECT ', '+ R.RES_NAME
FROM PUB.MSP_RESOURCES R
JOIN PUB.MSP_ASSIGNMENTS A ON A.RES_UID=R.RES_UID
WHERE A.TASK_UID=T.TASK_UID
AND R.RES_TYPE IN(2)
GROUP BY R.RES_NAME
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') AS RESOURCES
0
source to share