Imran Imran - 7 months ago 31
SQL Question

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?

Answer Source

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;