如何在SQL Server中使用动态SQL旋转数据-DEV365 开发者社区

SQL Server PIVOT运算符在您知道应该成为列的所有值时，很有用。但是，如果您不知道桌上的内容。如何将行变成列？

动态SQL可以帮助它。让我们在一个简单的示例中检查一下。有一个桌子Goods。食品和商店的数量可以无限。如果它总是只有3家商店和3个食品，我们可以使用标准PIVOT。

商店	食物	金额
存储1	马铃薯	50
存储5	马铃薯	150
存储12	马铃薯	300
存储1	番茄	220
存储5	番茄	180
存储12	番茄	60
存储1	cucumber	500
存储5	cucumber	10
存储12	cucumber	90

因此，我们需要将食品变成列。结果应该是这样：

商店	马铃薯	番茄	cucumber
存储1	50	220	500
存储5	150	180	10
存储12	30	60	90

让我们创建一个表并插入我们的数据：

CREATE TABLE Goods([Store] VARCHAR(255), [Food] VARCHAR(255), [Amount] INT);

INSERT INTO Goods(Store, Food, Amount)
VALUES
('Store 1', 'potato', 50),
('Store 5', 'potato', 150),
('Store 12', 'potato', 300),
('Store 1', 'tomato', 220),
('Store 5', 'tomato', 180),
('Store 12', 'tomato', 60),
('Store 1', 'cucumber', 500),
('Store 5', 'cucumber', 10),
('Store 12', 'cucumber', 90);

GO

我们需要两个临时表进行计算：

/*for turning rows into columns*/
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp 
CREATE TABLE #tmp(Store VARCHAR(255), Food VARCHAR(255))
GO
/*for final result*/
IF OBJECT_ID('tempdb..#result') IS NOT NULL
    DROP TABLE #result
GO

三个变量：

DECLARE @sql NVARCHAR(MAX) = '' /*dynamic string*/
        , @columns NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', ['+ Food +'] INT'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*column names for the final result table*/
        , @summary NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', SUM(['+ Food +']) AS ['+ Food +']'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*summing amount*/

SELECT @columns AS [columns], @summary AS [summary];

现在，让我们的食品变成列，然后将其安全到#tmp表

INSERT INTO #tmp 
SELECT DISTINCT Store, 
REPLACE(STUFF(ISNULL((SELECT DISTINCT ', 0 AS ['+ Food +']'
            FROM Goods rw
            FOR XML PATH('')),''),1,2,''),'0 AS ['+ Food +']', CAST(org.Amount AS VARCHAR) + ' AS ['+org.Food+']')
FROM Goods org;

SELECT * FROM #tmp;

现在，我们需要从列食品中创建列。为此，我们将使用动态SQL。

--create final table according to number of columns from @columns and insert data from #tmp
SET @sql = N'create table #result(food varchar(255),'+@columns+'); '+CHAR(10)+
            'insert into #result '+CHAR(10);

SELECT @sql = @sql + N'select '''+Store+''' as Store, '+Food+' union all '+CHAR(10) FROM #tmp;

--remove last 'union all'
SET @sql = REVERSE(STUFF(REVERSE(@sql),1,11,''));

SET @sql = @sql + N'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

sp_executesql过程的结果将为

如果您需要保留它，则可以创建一个额外的表并将数据插入其中。例如：

SET @sql = @sql + N'create table final_result(food varchar(255),'+@columns+');'+CHAR(10)+
                    'insert into final_result'+CHAR(10)+
                    'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

SELECT* FROM final_result;

还可以添加其他列，例如总量，列总数或行时的总数。这是非常灵活的方法。

Note ：如果您的SQL版本高于2017年，则可以使用STRING_AGG()函数来简化STUFF/XML语句。

如果您使用其他方法，请在下面的评论中告诉我。尝试一些新事物会很有趣。