We help IT Professionals succeed at work.

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

LJG
LJG asked
on
113 Views
Last Modified: 2017-03-22
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

ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
LJG

Author

Commented:
Scott
Thanks so much - This is exactly what I was looking for.
LJG
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
hmm, why ignoring the new functions of SQL Server?...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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.