How can I group by rows and columns?

Question

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.

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.

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.

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.

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.

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?

Answer 1

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.

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.

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.

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

Answer 2

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.

Edit: Added total values