CODEWITHSUNDEEP
sqlsql-server
user3191137
user3191137

Reputation: 135

SQL Server: Modifying this query

This query gives me all the information that I need, however I'm wanting to display it differently if possible. The current result: https://i.sstatic.net/0Gh7c.jpg

DECLARE @MainHospital varchar(50)='HOSPITAL 1';
SELECT MainEmail, chkOutpatient, chkPartB
FROM SurveyPicList
WHERE MainHospital = @MainHospital
GROUP BY MainHospital, MainEmail, chkOutpatient, chkPartB

I'm trying to return 2 different list of MainEmail comma-delimited if they = "on" in chkOutpatient and chkPartB. So only 2 cells of data as a result. 1 header of chkOutpatient with a list of comma dilmeted emails that = "on", and 1 header of chkPartB with the same.

So for chkPartB, something like this?https://i.sstatic.net/y54S2.jpg

SELECT DISTINCT ', ' + MainEmail AS chkPartB
FROM SurveyPicList
WHERE MainHospital = @MainHospital
AND chkPartB = 'on'

Please let me know if my question is unclear or if I need to give more info.

Upvotes: 0

Views: 59

Answers (1)

adrianm
adrianm

Reputation: 14726

WITH Outpatients AS (
    SELECT DISTINCT MainEmail
    FROM SurveyPicList
    WHERE MainHospital = @MainHospital
          AND chkOutpatient = 'on'
)
,OutpatientsRawCsv AS (
    SELECT (
       SELECT ',' + MainEmail
       FROM Outpatients
       FOR XML PATH('')
    ) AS Csv
)
,PartBs AS (
    SELECT DISTINCT MainEmail
    FROM SurveyPicList
    WHERE MainHospital = @MainHospital
          AND chkPartB = 'on'
)
,PartBRawCsv AS (
    SELECT (
       SELECT ',' + MainEmail
       FROM PartBs
       FOR XML PATH('')
    ) AS Csv
)
SELECT STUFF(OutpatientsRawCsv.Csv, 1, 1, '') AS OutpatientsCsv
      ,STUFF(PartBRawCsv.Csv, 1, 1, '') AS PartBCsv
FROM OutpatientsRawCsv
     CROSS JOIN PartBRawCsv

Upvotes: 1

Related Questions