Question

在与英国广播公司努力实现这一目标之后,我在此问:

在2008年SSRS中,Im使用一个矩阵,在选定月份使用一个栏组。第1栏是第一个选定月,在新表格启动12个月之后:

Month | Mar11 May11 Apr11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 Dec11 Jan12 Feb12
------+------------------------------------------------------------------------
Cat.1 |   3     4      5    7     8     9     1     3
Cat.2 |   4     2      3    6     1     3     2     5

Month | Mar12 May12 Apr12
------+------------------------------------------------------------------------
Cat.1 |   3     2     1
Cat.2 |   4     1     7

表中的表格是“重读”的(与一个大行组模拟的),表是全宽的直观。然而,在仅选定一个月时,布局太狭窄,眼看上去很 la:

Month | Mar
------+----
Cat.1 |  3

How do I create a matrix that having a fixed width (namely, the width of 12 columns), disregarding how many months are selected?

Answer 1

你可以采取两种方式:

在你的询问中采用不同的行文和一栏分类数值形成一种 Car框架。

SELECT
 C.MonthStarting
,R.Category
,D.InstanceCount

FROM ( -- column values
    SELECT DISTINCT MonthStarting
    FROM Calendar
    WHERE MonthStarting BETWEEN @StartDate AND @EndDate
) AS AS C

JOIN ( -- row values
    SELECT DISTINCT Category
    FROM SomeRelevantTables
) AS R
    ON 1 = 1 -- intentional cartesian product

LEFT JOIN (
    SELECT 
     Category
    ,MonthStarting
    ,COUNT(1) AS InstanceCount

    FROM SomeRelevantTables

    GROUP BY
         Category
        ,MonthStarting
) AS D
    ON C.MonthStarting = D.MonthStarting
    AND R.Category = D.Category

或者,你可以ot除布局外,在表格中显示数据而不是矩阵。

SELECT 
 T.Category
,SUM(CASE WHEN C.RowNumberAsc = 1 THEN 1 END) AS Column1
,SUM(CASE WHEN C.RowNumberAsc = 2 THEN 1 END) AS Column2
,SUM(CASE WHEN C.RowNumberAsc = 3 THEN 1 END) AS Column3
,SUM(CASE WHEN C.RowNumberAsc = 4 THEN 1 END) AS Column4

FROM SomeRelevantTables AS T

JOIN (
    SELECT
     C.*
    ,ROW_NUMBER()OVER(PARTITION BY MonthStarting) AS RowNumberAsc

    FROM (
        SELECT DISTINCT MonthStarting
        FROM Calendar
        WHERE MonthStarting BETWEEN @StartDate AND @EndDate
    ) AS C
) AS C
    ON T.MonthStarting = C.MonthStarting

GROUP BY
    T.Category

友情链接