T-SQL - only stuff / concatenate some columns and drop xml

Question

I have a table like this:

ID	C1	C2	seq
1	This is my re	ally long sentence	1
1	This is my re	stored in a	2
1	This is my re	really terrible format	3

And I need some SQL query or stored procedure to pull it out such that it reads "this is my really long sentence stored in a really terrible format"

select 
    case 
       when seq = '1' 
          then c1 else '' 
    end + stuff((select c2 from table t2 
                 where t2. id = t1.id and t2.seq = t1.seq
                 for xml path('')), 1, 1, '') 
from 
    table t1

I've got this going but it's putting XML in and I can't figure out why. Any help is appreciated.

Answer 1

Figured it out. The for xml doesn't remove the XML like I thought, only leading chars, I had to cast c1 to an nvarchar resulting in an unnamed column and no xml :shrug:

Answer 2

FOR XML will use the column name as a node name, so if you have

SELECT someCol
FROM tbl
FOR XML PATH('')

you will still get XML like this

<someCol>someValue</someCol>

The point of this concatenation trick is to get an unnamed column, so that there is no node name, just the values. So you just need to use any function or calculation on the value to remove the column name

In your case, STUFF is not necessary, because you have no separator.

Don't forget to use , TYPE).value('text()[1]','nvarchar(max)') to prevent XML escaping/entitization.

select 
    c1 + (
        select case when 1=1 then c2 end
        from table t2 
        where t2.id = t1.id and t2.seq >= 2
        for xml path(''), type).value('text()[1]','nvarchar(max)')
from 
    table t1
where seq = '1';

---

Of course, if you are on SQL Server 2017+, you can just use STRING_AGG

select 
    c1 + string_agg(cast(c2 as nvarchar(max)), '')
from 
    table t1
group by t1.ID, t1.c1;