需求:名称变成列,金额变成对应列的值
例如:
行转列之后:
创建对应的存储过程:
CREATE PROCEDURE [dbo].[getPivotTable]
AS
BEGIN
--4:动态PIVOT行转列
DECLARE @sql_str VARCHAR(MAX)
DECLARE @sql_col VARCHAR(MAX)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([name]) FROM [TABLE_CODE] GROUP BY [name]
PRINT (@sql_col)
SET @sql_str = '
SELECT * FROM (
SELECT [name],[amount] FROM [TABLE_CODE]) p PIVOT
(SUM([amount]) FOR [name] IN ( '+ @sql_col +') ) AS pvt'
PRINT ('------------')
PRINT (@sql_str)
EXEC (@sql_str)
END
调用存储过程:
EXEC getPivotTable;