SQL - Combine Multiple Rows of Data to One Row and Column

Question

I'm looking to create a SQL Server query that will combine resources with the same Task into one row/record string using three tables. My SQL Server query below doesn't seem to be working correctly and takes a very long time to execute and then errors out. Thanks!

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

Bring back three result sets and squish them together yourself. That's the easiest way. — , Jun 10 '15 at 00:39
See [this SO post](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) for how to do this. — Tim Biegeleisen, Jun 10 '15 at 00:42
Do you have indexes in your tables? It makes a lot of difference. I believe the IDs are indexes for Resource and Task tables. What about the [Assignment Table]? (http://sqlfiddle.com/#!3/4efb8/1) — Nizam, Jun 10 '15 at 02:29

score 0 · Answer 1 · answered Jun 10 '15 at 01:46

0

Please 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)

SQL Fiddle

answered Jun 10 '15 at 01:46

Nizam

4,569
3
43
60

Nizam, I've tried and it was running over 15 minutes to execute. My [TaskTable] has about 40,000 records. I think it would eventually complete but it would take quite a bit of time to run. Thanks for the help! – Paul Lor Jun 10 '15 at 02:06
What about your Resource Table? – Nizam Jun 10 '15 at 02:08
About 900 records in the [ResourceTable]. – Paul Lor Jun 10 '15 at 02:16
I've never done assemblies before and I'm pretty new to SQL server. Also not sure if special permission is needed to do assemblies in my environment either. – Paul Lor Jun 10 '15 at 02:22
Look my above comment. It is pretty easy to create aggregate functions. I have package a DLL to do this. I think it worth a try. – Nizam Jun 10 '15 at 02:23
I don't have the permissions to do assemblies. – Paul Lor Jun 10 '15 at 06:03
Then your best bet is create indexes (if they dont exist already) – Nizam Jun 10 '15 at 11:00
They don't exist already and I don't have the permissions to do indexes. I appreciate your help. The SQL Project Server does not have any built in indexes. Thanks! – Paul Lor Jun 10 '15 at 16:15

score 0 · Answer 2 · answered Jun 24 '15 at 00:50

Thanks to Nizam, I figured out that I should be using the tables in the database instead of views, since I do not have permissions to create indexes. The built-in indexes allowed 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

SQL - Combine Multiple Rows of Data to One Row and Column

2 Answers2