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

LJG
LJG used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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
LJG

Author

Commented:
Scott
Thanks so much - This is exactly what I was looking for.
LJG
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
hmm, why ignoring the new functions of SQL Server?...
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Developer

Commented:
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 DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial