Get column name(s) based on value

Hi Experts, I have a table with practice days for a team. I want to run a query that returns a comma separated string of the days. So for the image below, the required result is "Mondays, Thursdays, Fridays"

Table
My current query is;

Select [Team Name],[Captain],
Case 'True'
when Monday then 'Mon'
when Tuesday then 'Tue'
when Wednesday then 'Wed'
when Thursday then 'Thu'
when Friday then 'Fri'
End
FROM Practice
where id=11

Open in new window


But it only returns the first true value. If there is a neater way of doing it, that would be great too.
LVL 1
takwirirarIT Projects ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I didn't test it but can you try the following?
Select [Team Name],[Captain],
	PraticeDays = Case 
		when Monday then 'Mon,'
		end
		+
		Case 
			when Tuesday then 'Tue,'
		end
		+
		Case 
			when Wednesday then 'Wed,'
		end
		+
		Case 
			when Thursday then 'Thu,'
		end
		+
		Case 
			when Friday then 'Fri'
		End
FROM Practice
where id=11

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim CakalicSenior Developer/ArchitectCommented:
This page provides an example and description of how to use COALESCE to pivot a result set.
SQLServer COALESCE

Regards,
Jim
0
Scott PletcherSenior DBACommented:
Select [Team Name],[Captain],
    STUFF(
        CASE WHEN Monday = 'True' THEN ',Mon' ELSE '' END +
        CASE WHEN Tuesday = 'True' THEN ',Tue' ELSE '' END +
        CASE WHEN Wednesday = 'True' THEN ',Wed' ELSE '' END +
        CASE WHEN Thursday = 'True' THEN ',Thu' ELSE '' END +
        CASE WHEN Friday = 'True' THEN ',Fri' ELSE '' END
    , 1, 1, '') AS Practice_Days
FROM Practice
where id=11
0
takwirirarIT Projects ManagerAuthor Commented:
Thank you all. Vitor's answer was the nearest which I had to tweak a little for it to work. Jim's contribution on COALESCE was great. I am now trying to make the query neater using it and Scott's is correct too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.