Sql concatenation not working with case statement in sql

Question

I want to concatenate multiple rows into a single text string but also checking few conditions using case statement. My query always returns last value rather then concatenating all the values.

My sql query is as below

DECLARE @str varchar(1000)
SET @str = ''
SELECT @str = @str + 
     CASE WHEN SUBSTRING(value,CHARINDEX('|',value)+1,LEN(value)) <> '' THEN 
         '$' +REPLACE(value,'|',' - ')
     ELSE 
         '$' +ISNULL(REPLACE(value,'|',''),'') 
     END + ', ' 
FROM dbo.sometable          

print @str

this is certainly one way of doing it: I'd use VARCHAR(MAX) instead of VARCHAR(1000) and just concatenate the VALUE field as-is (or with a COALESCE) to check it is working, then add in your CASE logic. — davek, Aug 18 '14 at 13:09
The thing that drives me mad is that MS is unable or unwilling to provide a CONCAT(column, separator) aggregate function in TSQL! There are so many working hours lost due to this same recurring problem! — Oliver, Aug 18 '14 at 13:27

KM. · Answer 1 · 2014-08-18T13:38:17.940

your code works:

--I added this
DECLARE @str varchar(1000)
declare @sometable table (value varchar(50))
insert into @sometable values ('aaaa|bbbb|cccc|dddd')
insert into @sometable values ('1111|2222|3333|4444')
insert into @sometable values ('xx|yy|zz')

--your code unchanged, other than "dbo.sometable" to "@sometable"
SET @str = ''
SELECT @str = @str + 
     CASE WHEN SUBSTRING(value,CHARINDEX('|',value)+1,LEN(value)) <> '' THEN 
         '$' +REPLACE(value,'|',' - ')
     ELSE 
         '$' +ISNULL(REPLACE(value,'|',''),'') 
     END + ', ' 
FROM @sometable --<<only changed table name         

print @str

OUTPUT:

$aaaa - bbbb - cccc - dddd, $1111 - 2222 - 3333 - 4444, $xx - yy - zz,

I think your concatenation looks good, run this to check your query isn't just wrong:

SELECT value
FROM dbo.sometable

you may have a bad where or join if you have more details in your actual query.

---- EDIT ----

Also, you can not use ORDER BY in your query when concatenating this way. If you need to concatenate with an order by you can use can use something like this:

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (5,3,'A & Z')
insert into @YourTable VALUES (2,2,'B<&>B')

set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue --<<<<<<<<<<<<ORDER BY
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

OUTPUT:

HeaderValue ChildValues
----------- ---------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

here is a working example of your code:

DECLARE @str varchar(1000)
declare @sometable table (value varchar(50))
insert into @sometable values ('aaaa|bbbb|cccc|dddd')
insert into @sometable values ('1111|2222|3333|4444')
insert into @sometable values ('xx|yy|zz')

--your code unchanged, other than "dbo.sometable" to "@sometable"
SET @str = ''
SELECT @str = @str + STUFF(
                               (SELECT  ', ' +
                                         CASE WHEN SUBSTRING(value,CHARINDEX('|',value)+1,LEN(value)) <> '' THEN 
                                             '$' +REPLACE(value,'|',' - ')
                                         ELSE 
                                             '$' +ISNULL(REPLACE(value,'|',''),'') 
                                         END 
                                    FROM @sometable t2

                                    ORDER BY t2.value --<<<<<<<<<<<<ORDER BY
                                    FOR XML PATH(''), TYPE
                               ).value('.','varchar(max)')
                               ,1,2, ''
                          ) 



print @str

OUTPUT:

$1111 - 2222 - 3333 - 4444, $aaaa - bbbb - cccc - dddd, $xx - yy - zz

The code "works" if you don't mind it suddenly ceasing to work when the phase of the moon changes, data volumes change, etc. See [Remarks](http://msdn.microsoft.com/en-gb/library/ms189484.aspx): "Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row" — Damien_The_Unbeliever, Aug 18 '14 at 13:16
@ Damien_The_Unbeliever, from reading that link, I think you only have problems when you use `ORDER BY`, which is not the case here. This is method of concatenation was done for years, before the more recent XML trick: http://stackoverflow.com/a/7284932/65223 — KM., Aug 18 '14 at 13:20
@KM - you may choose to say "it only applies when `ORDER BY` is involved", but I'm sure I've seen examples where it's failed without an `ORDER BY`. And the advice in the documentation is "don't do this". The KB article demonstrates *one* way in which it's not guaranteed. — Damien_The_Unbeliever, Aug 18 '14 at 13:57

score 1 · Accepted Answer · answered Aug 18 '14 at 13:01

1

The most common way to do row concatenation in SQL server is to use FOR XML PATH

SELECT @str = STUFF((SELECT CASE WHEN SUBSTRING(value,CHARINDEX('|',value)+1,LEN(value)) <> '' 
                          THEN '$' +REPLACE(value,'|',' - ')
                          ELSE '$' +ISNULL(REPLACE(value,'|',''),'') 
                     END 
              FROM @t FOR XML PATH('')), 1, 1, '')

answered Aug 18 '14 at 13:01

Dimt

2,278
2
20
26

this will not work with data that contains characters like `&` and `<`. The XML concatenation example within my answer is slightly different and will work with those characters. – KM. Aug 18 '14 at 13:30
@KM. was not aware about that, just have visited and added to favorites the aforementioned post with your answer. Thanks +1 to your answer. – Dimt Aug 18 '14 at 14:26

Sql concatenation not working with case statement in sql

2 Answers2