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

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

SQL Server 2012

Thanks in advance for your help.
LJG

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.
LVL 2
LJGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

E.g.
DECLARE @vw_Home_Info TABLE
    (
      FN NVARCHAR(255) PRIMARY KEY ,
      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 );

SELECT  O.FN ,
        STUFF(
			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

0
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
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
LJGAuthor Commented:
Scott
Thanks so much - This is exactly what I was looking for.
LJG
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ste5anSenior DeveloperCommented:
hmm, why ignoring the new functions of SQL Server?...
0
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.
0
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 ;)
0
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.
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
SQL

From novice to tech pro — start learning today.