SSRS automatically aliasing a subquery column using FOR XML

Question

I have a query running against a SQL Server database. One of the columns in it uses the STUFF() function.

When I run the query in a SQL editor window, the resulting value looks like this:

When I try to use this query in an SSRS report, SSRS automatically puts a column alias within the STUFF() function, resulting in a value that looks like this:

<Expr1>, 1</Expr1><Expr1>, 2</Expr1><Expr1>, 3</Expr1><Expr1>, 4</Expr1>

The only difference between the two is that when I save the query in SSRS, the term "AS Expr1" is added automatically (see code below).

How can I prevent SSRS from adding this alias automatically and run the query as it is?

   SELECT 
        FirstName, 
        LastName, 
        CourseTitle,
        LastLoginDate,
        NoOfModules,
        COUNT(CourseCompleted) AS ModulesStarted,
        STUFF(
          (
            SELECT ','  + CAST(CourseModule AS varchar(20)) -- SSRS puts "AS Expr1" here
            FROM EDSF 
            WHERE 
                FirstName = e.FirstName AND 
                LastName = e.LastName AND 
                Coursecompleted = '1' AND 
                CourseTitle = e.CourseTitle
            FOR XML PATH('')
          ),1,1,''
         ) AS CoursesCompleted
    FROM EDSF  e
    WHERE 
        Coursecompleted = '1' OR 
        Coursecompleted = '0'
    GROUP BY 
        FirstName, 
        LastName, 
        CourseTitle,
        LastLoginDate,
        NoOfModules;

EDIT: the code has been rearranged and the question reworded to make the issue clearer. I'm NOT having a problem aliasing the returned column itself: instead, SSRS is actually adding an alias to the subquery within my STUFF() function, resulting in extra junk within the field itself.

Give that column an alias *you* define, and SSRS won't feel the need to create one for you. — Aaron Bertrand, Oct 28 '14 at 17:27
HOW WILL I ADD AN ALIAS TO THE COLUMN. PLEASE *THIS IS THE COLUMN (CourseModule AS varchar(20))* — muyi ani, Oct 28 '14 at 17:46
its like repeating what ssrs is doing already, but now with longer word — muyi ani, Oct 28 '14 at 17:51
Why is this a problem? Why do you not want your column to have a reference-able alias? Why don't you put the code in a stored procedure so SSRS won't be able to change it? — Aaron Bertrand, Oct 28 '14 at 17:59
I dont want it to have an alias, all i want is the column to not to show up with , 1, 2, 3, 4 , instead of just showung, 1,2,3,4 — muyi ani, Oct 28 '14 at 18:02
at the moment when i submit my querry ssrs adds 'AS Expr1' to it automatically — muyi ani, Oct 28 '14 at 18:03
One more time: put your query in a stored procedure. Then the crappy visual designers in SSMS or SSRS won't be able to change your code. — Aaron Bertrand, Oct 28 '14 at 18:05
possible duplicate of [Concatenate distinct row values for field in reporting services](http://stackoverflow.com/questions/14046186/concatenate-distinct-row-values-for-field-in-reporting-services) — billinkc, Oct 28 '14 at 18:10
@billinkc [four dupes later](http://stackoverflow.com/questions/972616/string-aggregation-in-ssrs-2005/) but still a stored procedure seems a much cleaner solution. — Aaron Bertrand, Oct 28 '14 at 18:13

AHiggins · Accepted Answer · 2014-10-28T19:56:45.270

I think there's some confusion in the comments originally: the question isn't about giving a column an alias, the question is why SSRS won't allow the subquery within the STUFF() command to exist without an alias. XML is interpreting that and including the column name in the field values itself, converting the input of "1", "2", "3", and "4" to (formatting as code because it's being interpreted as HTML):

"<Expr1>, 1</Expr1><Expr1>, 2</Expr1><Expr1>, 3</Expr1><Expr1>, 4</Expr1>"

I'm pretty sure there's a way to tell FOR XML to ignore the column names, and I'm also pretty sure that the stored procedure option is a better way to go. That said, though, there are times a stored procedure isn't an option, and so I'll include a way of getting rid of the offending values with REPLACE()

SELECT 
    FirstName,
    LastName,
    CourseTitle,
    lastlogindate,
    Noofmodules, 
    COUNT(Coursecompleted) AS modulesstarted,
    REPLACE(REPLACE(REPLACE('<' + 
      STUFF((
        SELECT ','  + CAST(CourseModule AS varchar(20)) AS Expr1
        FROM Esdf 
        WHERE 
            FirstName = e.FirstName
            AND LastName = e.LastName
            AND Coursecompleted = '1'
            AND CourseTitle = e.CourseTitle
        FOR XML PATH('')),1,1,''
      ), '<Expr1>', ''), '</Expr1>', '') , ',', ''
     ) AS CoursesCompleted
FROM Esdf  e
WHERE 
    Coursecompleted = '1' OR
    Coursecompleted = '0'
GROUP BY 
    FirstName, 
    LastName, 
    CourseTitle,
    lastlogindate, 
    Noofmodules

Happy to help. Do check out the idea of using a stored procedure,as @aaronbertrand and billinkc suggested. I'd also suggest that you look over the edit I made to your original question: a lot of the confusion could have been avoided with some cleaner code formatting (using indentations and carriage returns) and a more detailed explanation. See [how to ask a good question](http://stackoverflow.com/help/how-to-ask) for more tips. — AHiggins, Oct 29 '14 at 12:12

SSRS automatically aliasing a subquery column using FOR XML

1 Answers1