CODEWITHSUNDEEP
sqlsql-server
Imran
Imran

Reputation: 37

SQL Remove last comma from Concatenated Columns

I'm trying to concatenate columns that I'm separating by adding a comma. I'd like to remove the last comma and the only way I can think to do this is using the convoluted way below:

SELECT        
id,

CASE WHEN LEN(
            ISNULL(CASE WHEN intExtraTime > 0 THEN Convert(varchar(3),intExtraTime) + '% Extra Time, ' END,'') + 
            ISNULL(CASE WHEN intprocessor = 1 THEN 'Laptop, ' END,'') +
            ISNULL(CASE WHEN intRest = 1 THEN 'Rest Break, ' END,'') + 
            ISNULL(CASE WHEN intReader = 1 THEN 'Reader, ' END,'') +
            ISNULL(CASE WHEN intScribe = 1 THEN 'Scribe, ' END,''))>0 
THEN 
    SUBSTRING(
                ISNULL(CASE WHEN intExtraTime > 0 THEN Convert(varchar(3),intExtraTime) + '% Extra Time, ' END,'') + 
                ISNULL(CASE WHEN intprocessor = 1 THEN 'Laptop, ' END,'') +
                ISNULL(CASE WHEN intRest = 1 THEN 'Rest Break, ' END,'') + 
                ISNULL(CASE WHEN intReader = 1 THEN 'Reader, ' END,'') +
                ISNULL(CASE WHEN intScribe = 1 THEN 'Scribe, ' END,''),1,
            LEN(
                ISNULL(CASE WHEN intExtraTime > 0 THEN Convert(varchar(3),intExtraTime) + '% Extra Time, ' END,'') + 
                ISNULL(CASE WHEN intprocessor = 1 THEN 'Laptop, ' END,'') +
                ISNULL(CASE WHEN intRest = 1 THEN 'Rest Break, ' END,'') + 
                ISNULL(CASE WHEN intReader = 1 THEN 'Reader, ' END,'') +
                ISNULL(CASE WHEN intScribe = 1 THEN 'Scribe, ' END,''))-1)
END

FROM            dbo.Candidate AS ExamOptions

Anyone know a better more cleaner way to do this?

Upvotes: 0

Views: 1030

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I assume you are using SQL Server, based on the syntax.

I wouldn't do this by removing the last comma. I would do this by removing the first comma using stuff():

SELECT (CASE WHEN intExtraTime > 0 or intprocessor = 1 or intRest = 1 or
                 intReader = 1 or intScribe = 1
             THEN STUFF(COALESCE(CASE WHEN intExtraTime > 0 THEN ', ' + Convert(varchar(3), intExtraTime) + '% Extra Time' END,  '')
                        COALESCE(CASE WHEN intprocessor = 1 THEN ', Laptop' END, '') +
                        COALESCE(CASE WHEN intRest = 1 THEN ', Rest Break' END, '') + 
                        COALESCE(CASE WHEN intReader = 1 THEN ', Reader' END, '') +
                        COALESCE(CASE WHEN intScribe = 1 THEN ', Scribe' END, ''),
                        1, 2, '')
       END)
FROM dbo.Candidate ExamOptions;

Upvotes: 2

Related Questions