SQL Server PIVOT with Multiple Aggregate Columns and Totals Columns

Question

I'd like to produce a pivot table with 2 sum aggregates per pivot column. Then to the right of the pivot columns I'd like to have some total columns. Finally, the number of pivot columns is dynamic. My preferred result would look like this:

My data looks like this:

From this answer, I've gotten pretty close to solving it. Here is what I have:

SELECT *
FROM (
    SELECT B.SiteID, R.BuildingID, C.*
    FROM Rooms R JOIN Buildings B
        ON R.BuildingID = B.BuildingID
    CROSS APPLY (
        VALUES(RTRIM(RoomType) + ' NASF', AreaNASF)
             ,(RTRIM(RoomType) + ' RSF', AreaRSF)
        ) C (Item,Value)
) src
PIVOT (
    SUM([Value])
    FOR [Item] IN ([CONFERENCE NASF], [CONFERENCE RSF], [OFFICE NASF], [OFFICE RSF], [STORAGE NASF], [STORAGE RSF])
) pvt

Which produces:

I'm getting the impression that I'll have to do the two row header outside SQL. What I need help with is how to add the total columns. Also, is there a better solution for dynamic columns other than the STUFF solution I've seen many places?

Here is the SQL to create the sample data:

CREATE TABLE Buildings (
    BuildingID CHAR(12),
    SiteID CHAR(12),
    Name VARCHAR(100),
    CONSTRAINT PK_Building PRIMARY KEY (BuildingID)
);

CREATE TABLE Rooms (
    BuildingID CHAR(12),
    FloorID CHAR(4),
    RoomID CHAR(8),
    RoomType CHAR(16),
    Dept CHAR(16),
    AreaNASF NUMERIC(12,2),
    AreaRSF NUMERIC(12,2),
    CONSTRAINT FK_Rooms_BuildingID FOREIGN KEY (BuildingID) REFERENCES Buildings(BuildingID),
    CONSTRAINT PK_Rooms PRIMARY KEY (BuildingID, FloorID, RoomID)
);

INSERT INTO Buildings (BuildingID, SiteID, Name) VALUES 
    ('100', 'Main', 'Headquarters'),
    ('200', 'Main', 'Technology'),
    ('300', 'Fleet', 'Fleet')

INSERT INTO Rooms (BuildingID, FloorID, RoomID, RoomType, Dept, AreaNASF, AreaRSF) VALUES
     ('100', '01', '101', 'CONFERENCE', 'FINANCE', 206.84, 207.00)
    ,('100', '01', '102', 'OFFICE', 'FINANCE', 100.55, 101.00)
    ,('100', '01', '103', 'OFFICE', 'FINANCE', 100.87, 101.00)
    ,('100', '02', '201', 'STORAGE', 'FINANCE', 56.15, 0.00)
    ,('100', '02', '202', 'CONFERENCE', 'FINANCE', 164.93, 160.00)
    ,('200', '01', '101', 'OFFICE', 'IT', 95.50, 96.00)
    ,('200', '01', '102', 'OFFICE', 'IT', 100.64, 100.00)
    ,('200', '01', '103', 'CONFERENCE', 'IT', 220.19, 220.00)
    ,('200', '01', '104', 'STORAGE', 'IT', 50.25, 0.00)
    ,('200', '02', '201', 'OFFICE', 'HR', 65.82, 66.00)
    ,('300', '01', '101', 'OFFICE', 'MAINTENANCE', 65.82, 66.00)
    ,('300', '01', '102', 'OFFICE', 'MAINTENANCE', 65.82, 66.00)

Answer 1

Here is one way using sum()Over() windowed aggregate function. Trick here is preaggregating the AreaNASF/AreaRSF column for each BuildingID before pivoting

SELECT *
FROM   (SELECT B.SiteID,
               R.BuildingID,
               TotalAreaNASF,
               TotalAreaRSF,
               C.*
        FROM   (SELECT TotalAreaNASF = Sum(AreaNASF)OVER(partition BY BuildingID),
                       TotalAreaRSF= Sum(AreaRSF)OVER(partition BY BuildingID),*
                FROM   Rooms) R
               JOIN Buildings B
                 ON R.BuildingID = B.BuildingID
               CROSS APPLY ( VALUES(Rtrim(RoomType) + ' NASF',AreaNASF),
                                   (Rtrim(RoomType) + ' RSF',AreaRSF) ) C (Item, Value)) src
       PIVOT ( Sum([Value])
             FOR [Item] IN ([CONFERENCE NASF],
                            [CONFERENCE RSF],
                            [OFFICE NASF],
                            [OFFICE RSF],
                            [STORAGE NASF],
                            [STORAGE RSF]) ) pvt 

if the number of RoomTypes is unknown then here is the dynamic version

declare @col_list varchar(max),
    @sql varchar(max)

set @col_list = stuff((select distinct ','+QUOTENAME(Rtrim(RoomType) + ' NASF')+','+QUOTENAME(Rtrim(RoomType) + ' RSF') from #Rooms for xml path('')),1,1,'')


set @sql = '
SELECT SiteID, BuildingID, '+@col_list+', [Total NASF], [Total RSF]
FROM   (SELECT B.SiteID,
               R.BuildingID,
               [Total NASF],
               [Total RSF],
               C.*
        FROM   (SELECT [Total NASF] = Sum(AreaNASF)OVER(partition BY BuildingID),
                       [Total RSF] = Sum(AreaRSF)OVER(partition BY BuildingID),*
                FROM   Rooms) R
               JOIN Buildings B
                 ON R.BuildingID = B.BuildingID
               CROSS APPLY ( VALUES(Rtrim(RoomType) + '' NASF'',AreaNASF),
                                   (Rtrim(RoomType) + '' RSF'',AreaRSF) ) C (Item, Value)) src
       PIVOT ( Sum([Value])
             FOR [Item] IN ('+@col_list+') ) pvt ' 

print @sql
exec (@sql)