SQL 查询透视在案例语句内出错

Question

下面的代码看起来应该可以工作，但我得到Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. 我认为这是指 avg（testResults） 在testrgstr_testname上的枢轴。

我有一张表格，上面写满了一天中不同时间的测试结果。 我想将某个日期范围内的污泥、灰尘和颗粒测试的所有结果平均到一行。

这导致了枢轴的使用。 唯一的问题是测试名称会根据一年中的时间从 JAMES 到 FRANK 再到其他名称而变化，这导致使用 case 语句来允许不同的测试名称，从而允许在透视后使用不同的列。

对于任何错误，我们深表歉意，因为这是我生产代码的精简版本，我无法对其进行测试。

跟测试数据为 （       选择测试名称，转换（十进制（10,2），结果）作为测试结果  从测试注册 其中结果日期介于 @pFromDate 和 @pToDate 之间   和（testrgstr_testname像"%污泥%"    或testrgstr_testname，如"%灰尘%"    或testrgstr_testname如"%粒子%"）)选择案例    当@pCampType='詹姆斯' 那么        (            选择 [JDTD 詹姆斯切割污泥] 作为s_at，[JDTD 詹姆斯切割污泥 AO] 作为s_ao，                   [JDTD JAMES Dust AT] 饰演 d_at， [JDTD JAMES Dust AO）] 饰演 d_ao，                   [JDTD JAMES Particle_AT] 饰演 p_at， [JDTD JAMES Particle_AO] p_ao            来自测试数据            支点            (                平均（测试结果）                对于testrgstr_testname                在（[JDTD 詹姆斯切割污泥 AT]，[JDTD JAMES 切割污泥 AO]，                    [JDTD JAMES Dust AT， [JDTD JAMES DUST AO]，                    [JDTD JAMES Particle_AT]，[JDTD JAMES Particle_AO]）            ） 作为 PVT        )    当@pCampType="弗兰克"时，则        (            选择 [JDTD 弗兰克切割污泥] 作为s_at，[JDTD 詹姆斯弗兰克污泥 AO] 作为s_ao，                   [JDTD FRANK Dust AT] 饰演 d_at， [JDTD FRANK Dust AO）] 饰演 d_ao，                   [JDTD FRANK Particle_AT] 饰演p_at，[JDTD FRANK Particle_AO] 饰演p_ao            来自测试数据            支点            (                平均（测试结果）                对于testrgstr_testname                在（[JDTD 詹姆斯切割污泥 AT]，[JDTD JAMES 切割污泥 AO]，                    [JDTD JAMES Dust AT， [JDTD JAMES DUST AO]，                    [JDTD JAMES Particle_AT]，[JDTD JAMES Particle_AO]）            ） 作为 PVT        )    还        (            选择"错误"        )    结束    

Answer 1

该问题是由于您应该从 THEN 返回标量这一事实引起的：

        SELECT [JDTD JAMES Cutting Sludge AT] as s_at, [JDTD JAMES Cutting Sludge AO] as s_ao,
               [JDTD JAMES Dust AT] as d_at, [JDTD JAMES Dust AO)] as d_ao,
               [JDTD JAMES Particle_AT] as p_at, [JDTD JAMES Particle_AO] p_ao
        ...

返回结果集，而不是单个值。

您可以使用 IF 语句并针对每个案例执行一个语句 - @pCampType = 'JAMES'/@pCampType = 'FRANK' 或使用动态 SQL：

DECLARE @SQL NVARCHAR(MAX) = '
    WITH
    TestData as (
           SELECT TestName, CONVERT(DECIMAL(10,2),result) as TestResult
      FROM TestReg
     WHERE  ResultDate BETWEEN @pFromDate AND @pToDate
       and (testrgstr_testname like ''%Sludge%''
        or testrgstr_testname like ''%Dust%''
        or testrgstr_testname like ''%Particle%'')
    )'
DECLARE @InnerSelectSQL NVARCHAR(MAX) = N''
IF (@pCampType = 'JAMES')
    @InnerSelectSQL = N'
            SELECT [JDTD JAMES Cutting Sludge AT] as s_at, [JDTD JAMES Cutting Sludge AO] as s_ao,
                   [JDTD JAMES Dust AT] as d_at, [JDTD JAMES Dust AO)] as d_ao,
                   [JDTD JAMES Particle_AT] as p_at, [JDTD JAMES Particle_AO] p_ao '
ELSE IF (@pCampType = 'FRANK')
    @InnerSelectSQL = N'
            SELECT [JDTD FRANK Cutting Sludge AT] as s_at, [JDTD JAMES FRANK Sludge AO] as s_ao,
                   [JDTD FRANK Dust AT] as d_at, [JDTD FRANK Dust AO)] as d_ao,
                   [JDTD FRANK Particle_AT] as p_at, [JDTD FRANK Particle_AO] as p_ao '
SET @SQL = @SQL + CHAR(13) + CHAR(10) + InnerSelectSQL + CHAR(13) + CHAR(10) + N'
    FROM TestData 
            PIVOT 
            ( 
                avg(TestResult) 
                FOR testrgstr_testname 
                IN ([JDTD JAMES Cutting Sludge AT],[JDTD JAMES Cutting Sludge AO], 
                    [JDTD JAMES Dust AT, [JDTD JAMES Dust AO], 
                    [JDTD JAMES Particle_AT],[JDTD JAMES Particle_AO]) 
            ) as pvt'
Dynamic SQL is uglier, but you avoid repetition.