CODEWITHSUNDEEP
sql-servert-sql
user3191137
user3191137

Reputation: 135

SQL: How to CONCAT value

How can I return the values of MainEmail in the query below, delimited by commas and still count MDCselect? 

declare @MainHospital varchar(50)='hospital 1'
    select distinct mainhospital , f.Item, count(*) Count
    from SurveyPicList s
    cross apply splitstrings(s.MDCselect,':') as f
    WHERE MainHospital = @MainHospital
    GROUP BY MainHospital, f.Item
    ORDER BY Count DESC

To be clear the above returns this: https://i.sstatic.net/C8auk.jpg

So there were 3 separate entries/people that selected "02-Eye". I want to list out their emails(MainEmail) comma delimited. Please let me know if I am unclear.

Upvotes: 0

Views: 53

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Assuming from your use of CROSS APPLY that you are using SQL Server, and that it is at least version 2005, you can use XML to do the concatenation as follows:

declare @MainHospital varchar(50)='hospital 1';

select mainhospital , f.Item, count(*) Count
       ,Stuff(
           (select distinct ', ' + m.MainEmail
              from SurveyPicList m
             where m.MainHospital = @MainHospital
               and ':' + m.MDCselect + ':' like '%:' + f.Item + ':%'
            FOR XML PATH ('')),
           1, 2, '') as Emails
    from SurveyPicList s
    cross apply splitstrings(s.MDCselect,':') as f
    WHERE MainHospital = @MainHospital
    GROUP BY MainHospital, f.Item
    ORDER BY Count DESC

From the name I am assuming that splitstrings splits its first argument into items separated by its second argument. Hence I used like to check for f.Item in m.MDCselect in the WHERE condition of the subselect. Actually, what this WHERE condition is doing is collecting all the rows from another instance of the same table that match one record in the final grouped output.

Upvotes: 1

Related Questions