Get rid of the last Last comma and space in a SQL statement.

Is there an easy way to get rid of the Last comma and space in the results from the following SQL Statement.
Just a note: FN   =  first Name.

SQL Statement:
SELECT   [FN] + ' is --> ' + CASE WHEN [Elderly_YN] = 1 THEN 'Elderly, ' ELSE '' END + CASE WHEN [Disabled_YN] = 1 THEN 'Disabled, ' ELSE '' END + CASE WHEN [Veteran_YN] = 1 THEN 'Veteran, ' ELSE '' END + CASE WHEN [Single_YN]
                          = 1 THEN 'Single, ' ELSE '' END + CASE WHEN [Married_YN] = 1 THEN 'Married, ' ELSE '' END AS Expr2
FROM            dbo.vw_Home_Info

Mary is --> Elderly, Disabled,
James is --> Veteran,
Royal is --> Elderly, Disabled, Veteran, Married,
Cynthia is -->

SQL Server 2012

Thanks in advance for your help.

Somebody is going to ask:
Why do you have both single and married when someone can't be both.
Answer: I'm dealing with an existing database that I can't change the tables.
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.

ste5anSenior DeveloperCommented:
First of all: even when it's an existing database, this "design" violates the rules of normalization, thus... well a nightmare.

      Elderly_YN INT ,
      Disabled_YN INT ,
      Veteran_YN INT ,
      Single_YN INT ,
      Married_YN INT

INSERT  INTO @vw_Home_Info
VALUES  ( N'Mary ', 1, 1, 0, 0, 0 ),
        ( N'James ', 0, 0, 1, 0, 0 ),
        ( N'Royal ', 1, 1, 1, 0, 1 ),
        ( N'Cynthia ', 0, 0, 0, 0, 0 );

			IIF(O.Elderly_YN = 1, ', Elderly', '') + 
			IIF(O.Disabled_YN = 1, ', Disabled', '') + 
			IIF(O.Veteran_YN = 1, ', Veteran', '') + 
			IIF(O.Single_YN = 1, ', Single', '') + 
			IIF(O.Married_YN = 1, ', Married', '')
			, 1, 2, '') AS StatusText
FROM    @vw_Home_Info O;

Open in new window

Scott PletcherSenior DBACommented:
Using STUFF is by far the easiest way.  The idea is that a value is *always* preceded by the ', ', and then the first two bytes are *always* removed.  This means the final string is *always* right (hopefully :-) ).  Doing this at the front of the string rather than the end eliminates the need to know the total length of the final string in order to do the trim.

SELECT   [FN] + ' is --> ' + STUFF(
    CASE WHEN [Elderly_YN] = 1 THEN ', Elderly' ELSE '' END +
    CASE WHEN [Disabled_YN] = 1 THEN ', Disabled' ELSE '' END +
    CASE WHEN [Veteran_YN] = 1 THEN ', Veteran' ELSE '' END +
    CASE WHEN [Single_YN] = 1 THEN ', Single' ELSE '' END +
    CASE WHEN [Married_YN] = 1 THEN ', Married' ELSE '' END, 1, 2, '') AS Expr2
FROM            dbo.vw_Home_Info

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
LJGAuthor Commented:
Thanks so much - This is exactly what I was looking for.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ste5anSenior DeveloperCommented:
hmm, why ignoring the new functions of SQL Server?...
Scott PletcherSenior DBACommented:
Because they're not SQL syntax, they're a programming construct tacked onto SQL.  No other SQL will ever use that.  CASE is ANSI-standard SQL.  Also, if you have to support lower versions of SQL, IIF is not available (one of the only good things about earlier versions of SQL :-) ).

I hadn't seen your comment when I posted mine.
ste5anSenior DeveloperCommented:
I had never the case, that I need SQL in multiple dialects in over 20 years. So, yes, it's not ANSI. But it increases readability.. and SQL Server 2012 was explicitly mentioned ;)
Scott PletcherSenior DBACommented:
To me it decreases readability, not increases it.  It's so out of place with the other language elements and style.  It's like hitting German in the middle of English.
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

From novice to tech pro — start learning today.