給定任意表、行標題欄、列標題欄、數值欄,動態產生交叉表(樞紐分析),如 SPSS/Excel 樞紐表功能。
CREATE PROCEDURE [dbo].[Pro_CrossTable]
@tableName NVARCHAR(255), -- 來源表
@colName1 NVARCHAR(255), -- 行標題 (轉為列)
@colName2 NVARCHAR(255), -- 列標題 (保持行)
@valName NVARCHAR(255) = N'cnt' -- 數值欄,預設 COUNT(*)
AS
BEGIN
SET NOCOUNT ON;
-- 1. 建立暫存中間表:行標題、列標題、數值
IF OBJECT_ID('tempdb..#CrossTmp') IS NOT NULL DROP TABLE #CrossTmp;
CREATE TABLE #CrossTmp (
RowLabel NVARCHAR(500) NULL,
ColLabel NVARCHAR(500) NOT NULL,
Val FLOAT NULL
);
-- 動態彙總 SQL
DECLARE @sql NVARCHAR(MAX) = N'
INSERT INTO #CrossTmp (RowLabel, ColLabel, Val)
SELECT ' + QUOTENAME(@colName1) + N', ' + QUOTENAME(@colName2) + N', COUNT(*)
FROM ' + QUOTENAME(@tableName) + N'
WHERE ' + QUOTENAME(@colName1) + N' IS NOT NULL
GROUP BY ' + QUOTENAME(@colName1) + N', ' + QUOTENAME(@colName2) + N';';
EXEC sp_executesql @sql;
-- 2. 動態產生欄位清單:[值1], [值2], ...
DECLARE @cols NVARCHAR(MAX), @sumCols NVARCHAR(MAX);
SELECT @cols = STUFF((
SELECT N'], [' + ColLabel FROM (
SELECT DISTINCT ColLabel FROM #CrossTmp
) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N']';
SELECT @sumCols = STUFF((
SELECT N'], SUM([' + ColLabel + N'])' FROM (
SELECT DISTINCT ColLabel FROM #CrossTmp
) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N']';
-- 3. 動態 PIVOT 查詢
SET @sql = N'
SELECT RowLabel, ' + @sumCols + N'
FROM #CrossTmp
PIVOT (SUM(Val) FOR ColLabel IN (' + @cols + N')) pvt
ORDER BY RowLabel;';
EXEC sp_executesql @sql;
END
GO
EXEC dbo.Pro_CrossTable
@tableName = N'temp_A063',
@colName1 = N'ageArrange', -- 行:年齡層
@colName2 = N'indate'; -- 列:進院日期
ColLabel 不同值展開為獨立欄位,聚合函數為 SUM(Val)。#CrossTmp 隔離動態 SQL,避免重複掃描大表。GROUPING SETS 或 ROLLUP)。@valName 允許指定數值欄而非固定 COUNT。(@colName1, @colName2) 上有索引支援 GROUP BY。