sql-server - SSRS 使用 FOR XML 自动为子查询列添加别名

Question

我有一个针对 SQL Server 数据库运行的查询。其中的一列使用 STUFF() 函数。

当我在 SQL 编辑器窗口中运行查询时，结果值如下所示:

1234

当我尝试在 SSRS 报告中使用此查询时，SSRS 会自动在 STUFF() 函数中放置一个列别名，从而产生如下所示的值:

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

两者之间的唯一区别是，当我在 SSRS 中保存查询时，会自动添加术语“AS Expr1”(请参见下面的代码)。

如何防止 SSRS 自动添加此别名并按原样运行查询？

   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;

编辑:代码已重新排列，问题已重新措辞以使问题更清晰。我在为返回的列本身设置别名时没有遇到问题:相反，SSRS 实际上是在我的 STUFF() 函数中向子查询添加别名，从而导致字段本身产生额外的垃圾。

Answer 1

我认为最初的评论存在一些困惑:问题不在于为列提供别名，问题在于为什么 SSRS 不允许在 STUFF() 命令中使用子查询没有别名的情况下存在。 XML 正在解释这一点，并将列名包含在字段值本身中，将“1”、“2”、“3”和“4”的输入转换为(格式化为代码，因为它被解释为 HTML):

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

我非常确定有一种方法可以告诉 FOR XML 忽略列名称，而且我也非常确定存储过程选项是更好的方法。尽管如此，有时存储过程不是一个选项，因此我将提供一种使用 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