Aggregate comma delimited data seperated into appropriate rows

Question

I'm writing a SQL query in SQL Server 2014, and I need to create a query returns a result set that contains a comma delimited list in each row that contains each District manager that's assigned to the school in said row. I am unable to use SQL Server 2017's String_agg function. I am using the method using the FOR XML PATH and STUFF like the one that's used in this thread: What is the meaning of SELECT ... FOR XML PATH(' '),1,1)?

My script is producing a comma delimited list, but it is producing a list containing every manager, whether the manager is assigned to that school or not, and it is returning that identical list for every row in the query result instead of showing a list of managers only assigned to that school district.

with district_Managers as 
(
    select
        rtrim(ltrim(CONCAT(p.person_first_name, ' ' , p.person_last_name))) as name,
        m.District_GUID as Manager_district_guid,
        s.District_GUID as School_district_guid,
        d.District_Name,
        s.School_Name, 
        s.school_guid
    from 
        Manager m
    inner join 
        district d on d.District_GUID = m.District_GUID
    inner join 
        person p on m.person_guid = p.person_guid
    inner join 
        school s on s.district_Guid = m.District_GUID
    where 
        m.Manager_position_text = 'DAI' and m.Manager_hire_flag = 'W'
)
select
    (stuff((select ','+ [name] 
            from district_Managers 
            group by Manager_district_Guid 
            for xml path('')), 1, 1, '')) as [Name], 
    School_Name AS [School Name], District_Name AS [District Name],
    (select count(*) 
     from dbo.Manager m 
     where m.district_guid = School_district_guid 
       and m.Manager_position_text = 'DAI' 
       and m.Manager_hire_flag = 'W') AS [Count]
from
    district_Managers
order by 
    [Count] desc, District_Name

How can I restrict the managers listed in the field to the one assigned to the school?

Edit: placed the for xml statement

Where is the `FOR XML` part of your query? – BJones Dec 30 '19 at 18:53 — BJones, Dec 30 '19 at 18:53
@Bjones uploaded the wrong sql. Fixed the post – ArmorCode Dec 30 '19 at 19:09 — ArmorCode, Dec 30 '19 at 19:09

BJones · Accepted Answer · 2019-12-30T19:24:31.953

You need to match the rows to the outer query. Note the line - where csl.School_district_guid = dm.School_district_guid... I'm making a few assumptions.

with district_Managers as 
    (
        select
        rtrim(ltrim(CONCAT(p.person_first_name, ' ' , p.person_last_name))) as name,
        m.District_GUID as Manager_district_guid,
        s.District_GUID as School_district_guid,
        d.District_Name,
        s.School_Name, 
        s.school_guid
        from Manager m
        inner join district d on d.District_GUID = m.District_GUID
        inner join person p on m.person_guid = p.person_guid
        inner join school s on s.district_Guid = m.District_GUID
        where m.Manager_position_text = 'DAI' and m.Manager_hire_flag = 'W'
    )
    select
    (
        stuff(
        (
            select ','+ [name] 
            from district_Managers csl
            where csl.School_district_guid = dm.School_district_guid
            group by Manager_district_Guid for XML path('')
        ),1,1,'') 

    ) as [Name], School_Name AS [School Name], District_Name AS [District Name],
    (select count(*) from dbo.Manager m where m.district_guid = School_district_guid and m.Manager_position_text = 'DAI' and m.Manager_hire_flag = 'W'
        ) AS [Count]
    FROM district_Managers dm
    Order By [Count] Desc, District_Name

If you only want to get distinct rows, then you could either use SELECT DISTINCT... or you can use MAX() around the other columns and GROUP BY the comma separated list.

Aggregate comma delimited data seperated into appropriate rows

1 Answers1