asked on 3/22/2017

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.

SQL Microsoft SQL Server

Last Comment

Scott Pletcher

3/22/2017

ste5an

3/22/2017

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

3/22/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

LJG

3/22/2017

ASKER

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

ste5an

3/22/2017

hmm, why ignoring the new functions of SQL Server?...

Scott Pletcher

3/22/2017

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

3/22/2017

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

3/22/2017

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.