MSSQL How to aggregate row from query (not table)

Question

I need to aggregate row into string in this query:

WITH requery AS 
    (SELECT  CAST ( Name AS VARCHAR(1000) ) as Folder, f1.ParentFolderID, unt.NetworkName, unt.Model, Marker
    FROM    [Base].[dbo].[Folders] as f1
    JOIN (
        SELECT u.NetworkName, m.Name as Model, u.ParentFolderID, ma.Name as Marker
        FROM Base.dbo.Units as u
            JOIN Base.dbo.Models as m
            ON u.ModelID=m.ModelID
            LEFT JOIN Base.dbo.MarkerLinks as ml
            ON u.ParentFolderID=ml.FolderID
            LEFT JOIN Base.dbo.MarkerS as ma
            ON ml.MarkerID = ma.MarkerID
        WHERE u.NetworkName LIKE 'pcname'   ) as unt ON unt.ParentFolderID = f1.FolderID
    UNION ALL
    SELECT  CAST ( (f.Name + '\' + f2.Folder) AS VARCHAR(1000) ) as Name, f.ParentFolderID, f2.NetworkName, f2.Model, f2.Marker
    FROM    [Base].[dbo].[Folders] f
    JOIN    requery as f2
    ON      f.FolderID = f2.ParentFolderID)
SELECT  NetworkName,Model,Folder,Marker
FROM    requery
WHERE ParentFolderID is NULL

it return data as

pcname model location user1
pcname model location user2
pcname model location user3

but I need

pcname model location user1,user2,user3

I know about OUTER APPLY (like there) but I don't understand how to use it here. thanks.

You need to use XML for this. The article you referenced should work quite easily. Here is another article that explains this process from a purely sql server standpoint. http://www.sqlservercentral.com/articles/comma+separated+list/71700/ — Sean Lange, Jan 23 '15 at 15:03

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

I'm #$#$#, just used this to last part of query and it's working!

SELECT  NetworkName,Model,Folder,Marker
FROM    requery
WHERE ParentFolderID is NULL

thanks 4 all and NiMa exclusively)

edited May 23 '17 at 10:26

Community

1
1

answered Jan 26 '15 at 15:36

deGrey

1
2

MSSQL How to aggregate row from query (not table)

1 Answers1