如何按行和列分组？

I can not find any question/answer that could help me to get close to my goal. I have the following table check code on SQL Fiddle.

我找不到任何可以帮助我接近目标的问题/答案。我在SQL Fiddle上有下表检查代码。

SITENAME RTYPE SEASON  CTRSTAT  CTRTYPE UNITS  STARTDATE  WKTYPE    INVSTAT
 site1    T2B    P     null      null    92      null     FRSA_P    Unsold
 site1    T2B    Pre   null      null    36      null     FRSA_P    Unsold
 site1    T2B    PRI   null      null    173     null     FRSA_P    Unsold
 site1    T2B    SPE   null      null    56      null     FRSA_P    Unsold
 site1    T2B    P    Complete   FRSA    2     2013-01-01 FRSA_P    Sold
 site1    T2B    Pre  Complete   FRSA    2     2013-01-01 FRSA_P    Sold
 site1    T2B    PRI  Complete   FRSA    2     2013-01-01 FRSA_P    Sold
 site1    T2B    P    Complete   FRSA    1     2013-03-05 FRSA_P    Sold
 site1    T2B    P    Complete   FRSA    3     2014-01-01 FRSA_P    Sold
 site1    T2B    Pre  Complete   FRSA    4     2014-01-01 FRSA_P    Sold
 site1    T3B    P     null      null    69      null     FRSA_P    Unsold
 site1    T3B    Pre   null      null    26      null     FRSA_P    Unsold
 site1    T3B    PRI   null      null    125     null     FRSA_P    Unsold
 site1    T3B    SPE   null      null    40      null     FRSA_P    Unsold
 site1    T3B    Pre  Complete   FRSA    2     2013-01-01 FRSA_P    Sold
 site1    T3B    P    Complete   FRSA    1     2014-01-01 FRSA_P    Sold
 site1    T3B    Pre  Complete   FRSA    2     2014-01-01 FRSA_P    Sold

Then somehow get to a report like the one on the following picture. If the InvStatus is unsold, the report should return Unsold, otherwise the CTRSTAT.

然后以某种方式得到如下图所示的报告。如果InvStatus未售出,则报告应返回Unsold,否则返回CTRSTAT。

Please do not pay attention to the totals and grand total, the image is from an excel using other data. Is just to have sample of the needed result.

请不要注意总计和总计,图像来自excel使用其他数据。只是要获得所需结果的样本。

EDIT: The dates columns correspond to a full year so for example the one with 2013-03-05 belongs to the 01/01/2013 column. In case of null date then they will go to the last year displayed, on this case 01/01/2014 but could be 2015 o whatever period is the last displayed.

编辑:日期列对应一整年,例如2013-03-05的日期属于01/01/2013列。如果是空日期,那么它们将进入显示的最后一年,在这种情况下01/01/2014但是可以是2015年o在最后显示的任何时间段。

I have tried several different approaches, using GROUP BY together with GROUPING and SETS of various types but none worked. CUBE and ROLLUP are not useful or at least I have not managed to get the correct result. Maybe just is not possible, at least not on an easy way.

我尝试了几种不同的方法,使用GROUP BY和GROUPING以及各种类型的SETS,但都没有。 CUBE和ROLLUP没用,或者至少我没有设法得到正确的结果。也许只是不可能,至少不是一个简单的方法。

Here is a sample of one of the many queries I have tried, trying to twist it to get something like the report, but still nothing close.

以下是我尝试过的众多查询之一的示例,试图扭曲它以获得类似报告的内容,但仍然没有任何接近。

SELECT
       sitename, rtype, season, ctrstat, ctrtype, SUM(units)
      ,startdate, invstat
  FROM tt
 GROUP BY
GROUPING SETS ((sitename,InvStat,ctrstat)
               ,(RType,ctrtype,season,startdate)
               ,()
              )

EDIT: Maybe should be better using the SSRS for this task?

编辑:也许应该更好地使用SSRS来完成这项任务?

2 个解决方案

#1

As you mention in your edit I think doing this i SSRS is the way to go. You can use a matrix to easily create the report you are looking for. By using the data given in your SQL fiddle you can quickly get something as shown below.

正如你在编辑中提到的那样,我认为这样做我的SSRS是要走的路。您可以使用矩阵轻松创建您要查找的报告。通过使用SQL小提琴中提供的数据,您可以快速获得如下所示的内容。

Edit: Added total values

编辑:添加总值

#2

While SSRS probably is a better option, here is how it can be done with SQL query. At the end, combining the two might be the best solution.

虽然SSRS可能是更好的选择,但这里是如何使用SQL查询完成的。最后,将两者结合起来可能是最好的解决方案。

WITH CTE_Pivot AS 
(
    SELECT
         sitename
        ,rtype
        ,season
        ,CASE WHEN invstat = 'Sold' AND ctrStat = 'Complete' AND YEAR(startdate)=2013 THEN units ELSE 0 END AS Complete2013
        ,CASE WHEN invstat = 'Sold' AND ctrStat = 'Complete' AND (YEAR(startdate)=2014 OR startdate IS NULL) THEN units ELSE 0 END AS Complete2014
        ,CASE WHEN invstat = 'Unsold' AND YEAR(startdate)=2013 THEN units ELSE 0 END AS Unsold2013
        ,CASE WHEN invstat = 'Unsold' AND (YEAR(startdate)=2014 OR startdate IS NULL) THEN units ELSE 0 END AS Unsold2014
        ,units AS Total
    FROM tt 
)
SELECT sitename,rtype,season
    ,SUM(Complete2013) AS Complete2013
    ,SUM(Complete2014) AS Complete2014
    ,SUM(Unsold2013) AS Unsold2013
    ,SUM(Unsold2014) AS Unsold2014
    ,SUM(Total) AS GrandTotal
FROM CTE_Pivot
GROUP BY GROUPING SETS ((sitename,rtype,season),(sitename,rtype),sitename)

SQLFiddle DEMO

First part is to pivot your columns. This is rather simple way, without using PIVOT function, but declaring conditions for each column in CASE statement. It assumes you have a fixed number of possible values (and columns) which might not be perfect, but creating dynamic columns require dynamic SQL which would bring this query to whole new level of complexity.

第一部分是旋转列。这是一种相当简单的方法,不使用PIVOT函数,而是在CASE语句中为每列声明条件。它假定您有可能不完美的固定数量的可能值(和列),但创建动态列需要动态SQL,这会使此查询达到全新的复杂程度。

After all in this case, I don't really think you'll have much different values for CTRSTAT and I guess you know upfront all possible values. Years are a bit more problematic, but still I guess there aren't that many. You can maybe go with "THIS Year, LAST Year, NEXT Year" to make it somewhat more adaptable for future.

毕竟在这种情况下,我真的不认为你对CTRSTAT会有很多不同的值,我想你早先知道所有可能的值。岁月有点问题,但我猜不会有那么多。您可以选择“今年,上一年,下一年”,以使其更适应未来。

Second part of query is just grouping by using simple grouping sets. ROLLUP would be same as this.

查询的第二部分只是使用简单的分组集进行分组。 ROLLUP与此相同。