SQL: How to CONCAT value

Question

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.

You did not state which database system. This question will need a database specific answer. (I did not down vote.) — FrankPl
– FrankPl, Commented Mar 19, 2014 at 21:01

FrankPl · Accepted Answer · 2014-03-19 21:35:34Z

1

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.

edited Mar 19, 2014 at 21:35

answered Mar 19, 2014 at 21:00

FrankPl

13.3k2 gold badges17 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3191137 Over a year ago

inserting the block 'stuff throws a hand full of errors. "The Stuff argument requires 4 arguments", couldn't bind f.Item and s.PicListId/PK

FrankPl Over a year ago

Sorry, I forgot the parentheses around the '' of the FOR XML PATH. I edited my answer.

user3191137 Over a year ago

Msg 137, Level 15, State 2, Line 20 Must declare the scalar variable "@MainHospital".

FrankPl Over a year ago

Sorry, I had just copied the SQL statement without the variable declaration, now I added that as well.

user3191137 Over a year ago

Column 'SurveyPicList.PicListId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|

Collectives™ on Stack Overflow

SQL: How to CONCAT value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related