TSQL Multiple Rows Into 1 Row

KANEDA 0149
KANEDA 0149 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Senior Developer
Commented:
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

Author

Commented:
Thank you ste5an, that worked!

Author

Commented:
Thank you, that did it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial