TSQL Multiple Rows Into 1 Row

Hello, need help to return multiple rows into 1 singular column with semicolon delimiter.  I tried the FOR XML PATH but got stuck because my original query requires a nested join.  Below is an example of the current results and my expected results.  Thanks in advance!

select a.WorkflowID, b.ResourceName
from table1 a
left join (select distinct ResourceID, ResourceName
			from table2
			where ResourceType = 4) b on a.ConstructID = b.ResourceID
where a.ConstructType = 4

Open in new window


multi_row_to_1_row.png
KANEDA 0149Asked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Common table expressions are your friend. E.g.

WITH Resources
AS ( SELECT DISTINCT ResourceID ,
                     ResourceName
     FROM   table2
     WHERE  ResourceType = 4 ) ,
     [Data]
AS ( SELECT a.WorkflowID ,
            b.ResourceName
     FROM   table1 a
            LEFT JOIN Resources b ON a.ConstructID = b.ResourceID
     WHERE  a.ConstructType = 4 )
SELECT   O.WorkflowID ,
         STUFF((   SELECT ', ' + I.ResourceName
                   FROM   [Data] I
                   WHERE  I.WorkflowID = O.WorkflowID
                   FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(255)') ,
               1 ,
               2 ,
               '') AS ResourcesText
FROM     [Data] O
GROUP BY O.WorkflowID;

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
KANEDA 0149Author Commented:
Thank you ste5an, that worked!
0
 
KANEDA 0149Author Commented:
Thank you, that did it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.