SQL Server多聚合列PIVOT及总计列、动态列实现咨询

Great job getting the pivot with paired SUM values for NASF/RSF working! Let's break down solutions for your two requests:

1. 添加总计列 #

For your static pivot query, you can add a calculated column at the end that sums up all the NASF and RSF values for each row. We use ISNULL to handle cases where a room type doesn't exist in a building (to avoid NULLs breaking the sum):

SELECT 
  SiteID,
  BuildingID,
  [CONFERENCE NASF],
  [CONFERENCE RSF],
  [OFFICE NASF],
  [OFFICE RSF],
  [STORAGE NASF],
  [STORAGE RSF],
  -- Calculate total area by summing all value columns (handle NULLs)
  ISNULL([CONFERENCE NASF], 0) + ISNULL([CONFERENCE RSF], 0) +
  ISNULL([OFFICE NASF], 0) + ISNULL([OFFICE RSF], 0) +
  ISNULL([STORAGE NASF], 0) + ISNULL([STORAGE RSF], 0) AS TotalArea
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

2. 替代STUFF的动态列实现（使用STRING_AGG） #

If you're using SQL Server 2017 or later, STRING_AGG is a cleaner, more readable alternative to the old STUFF + FOR XML PATH pattern for generating dynamic pivot columns. It lets you directly aggregate column names into a comma-separated list.

Here's a complete dynamic SQL solution that automatically adapts to new RoomTypes, includes the pivot columns, and generates the TotalArea calculation dynamically:

DECLARE @PivotColumns NVARCHAR(MAX), @TotalCalculation NVARCHAR(MAX);

-- Step 1: Get all unique Item values (formatted as quoted column names)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(Item), ', ')
FROM (
 SELECT DISTINCT RTRIM(RoomType) + ' NASF' AS Item FROM Rooms
 UNION ALL
 SELECT DISTINCT RTRIM(RoomType) + ' RSF' AS Item FROM Rooms
) AS UniqueItems;

-- Step 2: Build the TotalArea calculation (sum all columns, handle NULLs)
SELECT @TotalCalculation = STRING_AGG('ISNULL(' + QUOTENAME(Item) + ', 0)', ' + ')
FROM (
 SELECT DISTINCT RTRIM(RoomType) + ' NASF' AS Item FROM Rooms
 UNION ALL
 SELECT DISTINCT RTRIM(RoomType) + ' RSF' AS Item FROM Rooms
) AS UniqueItems;

-- Step 3: Assemble and execute the dynamic SQL
DECLARE @DynamicSQL NVARCHAR(MAX) = N'
SELECT 
  SiteID,
  BuildingID,
  ' + @PivotColumns + ',
  ' + @TotalCalculation + ' AS TotalArea
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 (' + @PivotColumns + ')
) pvt';

EXEC sp_executesql @DynamicSQL;

Why this works: #

• STRING_AGG simplifies generating the list of pivot columns without messy string manipulation.
• The dynamic TotalCalculation ensures we always include all current RoomType columns in the total, even if new types are added later.
• QUOTENAME prevents SQL injection and handles any special characters in RoomType names.

内容的提问来源于stack exchange，提问作者Ryan