-2

I have a query that has multiple case statements. I am trying to reduce the results to one row per USER_ID that has one of the columns (specialty_id) comma separated because some users have more than one specialty per user Id

I hope this is enough information.

In the example below, I would like to one row for User_id 479, with the Specialty column comma separated.

Here is my code

select personnel_contact.user_id,

case rb_committee_member.Role
when 1 then 'Member'
    when 3 then 'Chair'
    when 7 then 'Vice Chair'
    when 5002 then 'Board Coordinator'
    when 5003 then 'Consultant'
    end as Role,

case personnel_contact.is_scientist
when 'Yes' then 'Scientist'
when 'No' then 'Non-Scientist'
else 'Non-Scientist'
end as Scientist,

 Case personnel_contact_splty.specialty_id
when 1  then 'Cardiology'
when 2  then 'Endocrinology'
when 3  then 'General Practitioner'
when 4  then 'Gynecology'
when 5  then 'Internist'
when 6  then 'Medical Oncology'
when 7  then 'Neurology'
when 8  then 'Neurosurgeon'
when 9  then 'Nuclear Medicine'
when 10 then 'Oncologist'
when 11 then 'Orthopedist'
when 12 then 'Otolaryngology'
when 13 then 'Pediatric Oncology'
when 14 then 'Physician Assistant'
when 15 then 'Plastic Surgeon'
when 16 then 'Pulmonology'
when 17 then 'Radiation Oncology'
when 18 then 'Radiotherapy'
when 19 then 'Surgeon'
when 20 then 'Surgical Oncology'
when 21 then 'Urology'
when 22 then 'Community Member - Non-scientist'
when 23 then 'Physical Therapy'
when 24 then 'Family Medicine'
when 25 then 'Oncology'
when 26 then 'Radiology'
when 27 then 'Orthopaedic Surgery'
when 28 then 'Biochemistry'
when 29 then 'Pharmacology'
when 30 then 'University Counsel'
when 31 then 'Emergency Medicine'
when 32 then 'Neurosurgery'
when 33 then 'Nursing Administration'
when 34 then 'Geriatrics'
when 35 then 'Microbiology'
when 36 then 'Immunology'
when 37 then 'Surgery'
when 38 then 'Rheumatology'
when 39 then 'Administration'
when 40 then 'Anesthesiology'
when 41 then 'Hematology'
when 42 then 'Dermatology'
when 43 then 'Psychiatry'
when 44 then 'Pharmacy'
when 45 then 'Medicine/Pulmonary'
when 46 then 'HRPP Analyst'
when 47 then 'Pediatrics'
when 48 then 'Neonatology'
when 49 then 'Public Health'
when 50 then 'HIV Medicine'
when 51 then 'Infectious Disease'
when 52 then 'Community Member - Scientist'
when 54 then 'Internal Medicine'
when 55 then 'Prisoner Advocate'
when 56 then 'Transplant Medicine'
when 57 then 'Vascular Surgery'
when 58 then 'Obstetrics and Gynecology'
when 59 then 'Perinatology'
when 60 then 'Genetics'
when 61 then 'Neuroscience and Neuroimaging'
when 62 then 'Neurological Surgery'
when 63 then 'Gastrointestinal Medicine'
when 64 then 'Health Policy'
when 65 then 'Law'
when NULL then 'not'
end as 'Specialty'

from RB_COMMITTEE_MEMBER

join
personnel_contact on PERSONNEL_CONTACT.USER_ID = RB_COMMITTEE_MEMBER.USER_ID

join
PERSONNEL_CONTACT_SPLTY on personnel_contact_splty.user_id = personnel_contact.user_id
join 
user_department on USER_DEPARTMENT.USER_ID = personnel_contact.USER_ID

join
SYSTEM_DEPARTMENT on system_department.DEPARTMENT_ID = USER_DEPARTMENT.DEPARTMENT_ID

join
user_profile on PERSONNEL_CONTACT.USER_ID = USER_PROFILE.USER_ID

where rb_committee_id = 7 and USER_DEPARTMENT.DEFAULT_DEPT = 'Yes' and user_profile.LOGIN_ENABLED = 'Yes' and 
USER_PROFILE. status = 'Active' and RB_COMMITTEE_MEMBER.role not in (2, 5000)

group by RB_COMMITTEE_MEMBER.ROLE,  PERSONNEL_CONTACT.IS_SCIENTIST, PERSONNEL_CONTACT_SPLTY.SPECIALTY_ID, 
PERSONNEL_CONTACT.user_id

order by case 
when Role = '3' then 1
when role = '7' then 2
when role = '1' then 3 
when role = '5001' then 5 
when role = '5002' then 4
when role = '2' then 6
when role = '5000' then 7
when role = '5003' then 8
else role end, user_id
  • literally nothing good comes from comma-separated values in a single column ..Why de-normalize this nicely set table ? What is your use case? – eagle275 Nov 22 '19 at 9:20
  • Its a requirement for the report i am creating – steves86ta Nov 22 '19 at 20:30
-1

You can use the STRING_AGG function in SQL Server 2017+ to produce a "good enough" CSV, but only if you don't have any embedded commas in your data and do not care about ordering of delimited items.

WITH TestData AS (
    SELECT * FROM (
        VALUES 
            ('Soccer', 'World Cup')
            ,('Soccer', 'Long Season')
            , ('Football', 'High Scoring')
            , ('Football', 'Superbowl')
            , ('Football', 'Beer')
            , ('Baseball','Fall Classic')
            , ('Baseball','World Series')
            , ('Baseball','America''s Pastime')
            , ('Baseball','Peanuts')
            , ('Baseball','Cracker Jacks')
    ) V(Item, Tag)
)
SELECT Item, STRING_AGG(Tag, ',') as Tags 
FROM TestData
GROUP BY Item

Output Results

| improve this answer | |
  • I respect someone's choice to down vote, however feedback on why would be appreciated. Within the restrictions called out, it is a working example that appears to address the specific issue. – Graham Apr 27 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.