Avatar of LJG
LJG
 asked on

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.
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ste5an

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

ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
LJG

ASKER
Scott
Thanks so much - This is exactly what I was looking for.
LJG
ste5an

hmm, why ignoring the new functions of SQL Server?...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Scott Pletcher

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

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 Pletcher

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.