除了编写SQL查询语句在同一行中显示单独的列值之外，是否还有其他方法可供选择？

Question

我正在派生一条SQL查询语句来准备统计报告。在我的报告中，我需要在几个单独的列中显示值。这些column具有不同的数字范围。(I.e.1, 2-4, 5-10, 11-19, 20-49, TotalForEachCategory)。我如何制定我的SQL查询来做同样的事情呢？目前，这些值位于不同的行中。

我正在准备SSRS报告。为此，我需要开发SQL语句。报告中反映的列在SQL表中不是物理列。因此，我需要在SQL聚合时派生它们。例如，我需要显示属于不同车队范围的每个类别的“车辆车队承运商总数”。

目前我的结果如下所示。(错误)

                            Fleet Ranges
----------------------------------------------------------------------
|  1  |  2-4  |  5-10  | 11-19  |  20-49  | TotalForCategory
----------------------------------------------------------------------
| 70  |   0   |   0    |   0    |    0    |        70
| 0   |  38   |   0    |   0    |    0    |        38
| 0   |   1   |   0    |   0    |    0    |         1
| 0   |   0   |  27    |   0    |    0    |        27

​

​

我到目前为止所尝试的

SQL查询：

SELECT
    (CASE WHEN F.mot_averagefleetsize = 1 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_1',
    (CASE WHEN F.mot_averagefleetsize BETWEEN 2 AND 4 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_2-4',
    (CASE WHEN F.mot_averagefleetsize BETWEEN 5 AND 10 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_5-10',
    (CASE WHEN F.mot_averagefleetsize BETWEEN 11 AND 19 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_11-19',
    (CASE WHEN F.mot_averagefleetsize BETWEEN 20 AND 49 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_20-49'
FROM
    Table1 F
INNER JOIN 
    Table2 M ON M.column1 = F.column2
GROUP BY
    F.mot_averagefleetsize

我的预期结果应该如下所示：

                                  Fleet Ranges
----------------------------------------------------------------------
           |  1  |  2-4  |  5-10  | 11-19  |  20-49  | TotalForCategory
----------------------------------------------------------------------
Category #1| 70  |  39   |  27    |   0    |    0    |       136
           |

Answer 1

在派生表聚合之后使用SUM。您将需要指定分类字段

select 
categoryfield,
sum (Fleet_Range_1) Fleet_Range_1
sum (Fleet_Range_2-4) Fleet_Range_2-4
sum (Fleet_Range_5-10) Fleet_Range_5-10
sum (Fleet_Range_11-1) Fleet_Range_11-1
sum (Fleet_Range_20-49) Fleet_Range_20-49
from (
select
categoryfield
,(CASE WHEN F.mot_averagefleetsize = 1 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_1'
,(CASE WHEN F.mot_averagefleetsize BETWEEN 2 AND 4 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_2-4'
,(CASE WHEN F.mot_averagefleetsize BETWEEN 5 AND 10 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_5-10'
,(CASE WHEN F.mot_averagefleetsize BETWEEN 11 AND 19 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_11-19'
,(CASE WHEN F.mot_averagefleetsize BETWEEN 20 AND 49 THEN COUNT(M.mot_carriernumbername) ELSE 0 END) AS 'Fleet_Range_20-49'
from Table1 F
inner join Table2 M on M.column1 = F.column2
group by F.mot_averagefleetsize, categoryfield
) x
group by categoryfield