动态枢轴表
很抱歉，这个问题很长，但是这包含了我用来测试场景的所有SQL，希望能够清楚表明我在做什么。
我构建了一些动态SQL来在SQL Server 2005中生成PIVOT表。
以下是执行此操作的代码。 用各种选择显示原始数据使用GROUP BY的值和PIVOT中的值，因为我想要它们。
BEGIN TRAN
--Create the table
CREATE TABLE #PivotTest
(
ColumnA nvarchar(500),
ColumnB nvarchar(500),
ColumnC int
)
--Populate the data
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'X', 1)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Y', 2)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Z', 3)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'X', 4)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Y', 5)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Z', 6)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'X', 7)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Y', 8)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Z', 9)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'X', 10)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'Y', 11)
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'Z', 12)
--The data
SELECT * FROM #PivotTest
--Group BY
SELECT
ColumnA,
ColumnB,
SUM(ColumnC)
FROM
#PivotTest
GROUP BY
ColumnA,
ColumnB
--Manual PIVOT
SELECT
*
FROM
(
SELECT
ColumnA,
ColumnB,
ColumnC
FROM
#PivotTest
) DATA
PIVOT
(
SUM(DATA.ColumnC)
FOR
ColumnB
IN
(
[X],[Y],[Z]
)
) PVT
--Dynamic PIVOT
DECLARE @columns nvarchar(max)
SELECT
@columns =
STUFF
(
(
SELECT DISTINCT
', [' + ColumnB + ']'
FROM
#PivotTest
FOR XML PATH('')
), 1, 1, ''
)
EXEC
('
SELECT
*
FROM
(
SELECT
ColumnA,
ColumnB,
ColumnC
FROM
#PivotTest
) DATA
PIVOT
(
SUM(DATA.ColumnC)
FOR
ColumnB
IN
(
' + @columns + '
)
) PVT
')
--The data again
SELECT * FROM #PivotTest
ROLLBACK
任何时候，我产生任何动态SQL，我总是知道SQL注入攻击。 因此，我在其他INSERT语句中添加了以下行。
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'FOO])) PVT; DROP TABLE #PivotTest;SELECT ((GETDATE()--', 1)
当我现在运行SQL时，请注意，EXEC部分会丢弃#PivotTest表，从而导致最后一个SELECT失败。
所以我的问题是，有没有人知道一种方法来执行动态PIVOT而不冒着SQL注入攻击风险？
我们做了很多类似于您的示例的工作。 我们并不担心SQL禁令，部分原因是我们完全控制了数据的完整控制 - 恶意代码无法通过ETL进入我们的数据仓库。
一些想法和建议：
哈。 花了所有的时间来记住函数QUOTENAME（）。 一个快速测试似乎表明，将它添加到您的代码就像这样会工作（你会得到一个错误，而不是一个删除临时表）：
SELECT
@columns =
STUFF
(
(
SELECT DISTINCT
', [' + quotename(ColumnB, ']') + ']'
FROM
#PivotTest
FOR XML PATH('')
), 1, 1, ''
)
这应该适用于pivot（和unpivot）的情况，因为你几乎总是需要[括号]你的值。
有点重构...
CREATE PROCEDURE ExecutePivot (
@TableName sysname,
@GroupingColumnName sysname,
@AggregateExpression VARCHAR(256),
@SelectExpression VARCHAR(256),
@TotalColumnName VARCHAR(256) = 'Total',
@DefaultNullValue VARCHAR(256) = NULL,
@IsExec BIT = 1)
AS
BEGIN
DECLARE @DistinctGroupedColumnsQuery VARCHAR(MAX);
SELECT @DistinctGroupedColumnsQuery = CONCAT('SELECT DISTINCT ',@GroupingColumnName,' FROM ',@TableName,';');
DECLARE @DistinctGroupedColumnsResult TABLE ( [row] VARCHAR(MAX) );
INSERT INTO @DistinctGroupedColumnsResult EXEC(@DistinctGroupedColumnsQuery);
DECLARE @GroupedColumns VARCHAR(MAX);
SELECT @GroupedColumns = STUFF ( ( SELECT DISTINCT CONCAT(', ',QUOTENAME([row])) FROM @DistinctGroupedColumnsResult FOR XML PATH('') ), 1, 1, '' );
DECLARE @GroupedColumnsNullReplaced VARCHAR(MAX);
IF(@DefaultNullValue IS NOT NULL)
SELECT @GroupedColumnsNullReplaced = STUFF ( ( SELECT DISTINCT CONCAT(', ISNULL(',QUOTENAME([row]),',',@DefaultNullValue,') AS ',QUOTENAME([row])) FROM @DistinctGroupedColumnsResult FOR XML PATH('') ), 1, 1, '' );
ELSE
SELECT @GroupedColumnsNullReplaced=@GroupedColumns;
DECLARE @ResultExpr VARCHAR(MAX) = CONCAT('
; WITH cte AS
(
SELECT ',@SelectExpression,', ',@GroupedColumns,'
FROM ',@TableName,'
PIVOT ( ',@AggregateExpression,' FOR ',@GroupingColumnName,' IN (',@GroupedColumns,') ) as p
)
, cte2 AS
(
SELECT ',@SelectExpression,', ',@GroupedColumnsNullReplaced,'
FROM cte
)
SELECT ',@SelectExpression,', ',REPLACE(@GroupedColumns,',','+'),' AS ',@TotalColumnName,', ',@GroupedColumns,'
FROM cte2;
');
IF(@IsExec = 1) EXEC(@ResultExpr);
ELSE SELECT @ResultExpr;
END;
用法示例：
select schema_id, type_desc, 1 as Item
into PivotTest
from sys.objects;
EXEC ExecutePivot 'PivotTest','type_desc','SUM(Item)','schema_id','[Total Items]','0',1;
上一篇: Dynamic PIVOT Table