Mssql subquery inside a substring I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them into a single string by appending them with a separator &quot;;&quot;.
This is what I need:
(SUBSTRING(
    (SELECT 
                    d1.name +'; '
    FROM   
                    data1 d1 
                    inner join data2 d2
                    on d1.id = d2.id
    WHERE   
                    d1.id = dOfOutterSelect.id 
    ORDER BY
                    d1.CreatedOn FOR XML PATH('') 

    ),
LENGTH(),1)) 

As &quot;Row name&quot;,
Use the STUFF(...) T-SQL function to strip the last character off.  First REVERSE the string from the query results, then use STUFF, then REVERSE the result again.
Like this:
SELECT REVERSE(STUFF(REVERSE('this is my string'), 1, 1, ''))

The result looks like:




Results




this is my strin




Here's the Microsoft Docs pages for STUFF and REVERSE.
Actually, looking at your query more closely, you can refine it slightly to eliminate the use of the REVERSE function.  Take the example below, where I'm concatenating the names of all the columns in the database with a semi-colon separator.
SELECT STUFF((
    SELECT N'; ' + c.name
    FROM sys.columns c
    FOR XML PATH (N'')
    ), 1, 2, N'');

I add the semi-colon at the start of the string, then simply use STUFF to replace the first 2 characters with &quot;nothing&quot;.
So the results look like:




Results




Column1; Column2; Column3




Newer versions of SQL Server (2017+) support the use of the STRING_AGG aggregate which can significantly simplify this kind of query.  To get the same output as my query above, using STRING_AGG, you'd write it like this:
SELECT STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ')
FROM sys.columns c;

There is CONVERT(nvarchar(max), ...) wrapped around the c.name column to coerce the STRING_AGG function into using a string of sufficient length to contain all the column names in my database.  If you leave that out, SQL Server reports an error:

Msg 9829, Level 16, State 1, Line 10
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.