转发： 一个动态行转列的数据透视过程TSQL，还支持临时表

原文：https://www.mssqltips.com/sqlservertip/6245/sql-server-dynamic-pivot-query/

 

1.存储过程

CREATE PROCEDURE dbo.usp_Dyna_Pivot ( @unknownValsCol NVARCHAR (100), @objNameToPivot NVARCHAR (100), @aggFuncOfPivot NVARCHAR (3), @aggColOfPivot NVARCHAR (100), @leadColPivot NVARCHAR (100)) AS BEGIN DECLARE @columns NVARCHAR (2000), @tsql NVARCHAR (2000) DECLARE @distinctVals TABLE (val NVARCHAR (50)) SET NOCOUNT ON SET @columns = N''; SET @tsql = CONCAT ('SELECT DISTINCT ', @unknownValsCol,' FROM ',@objNameToPivot) INSERT @distinctVals EXEC (@tsql) SELECT @columns += CONCAT ('[', Val,']',',') FROM @distinctVals SET @columns = LEFT (@columns, LEN (@columns) - 1) SET @tsql = CONCAT ( 'SELECT ', @leadColPivot, ',', @columns,' FROM ',' ( SELECT ',@leadColPivot,',', @aggColOfPivot,',', @unknownValsCol, ' FROM ', @objNameToPivot, ') as t ', ' PIVOT (', @aggFuncOfPivot, '(', @aggColOfPivot, ')',' FOR ', @unknownValsCol, ' IN (', @columns,')) as pvt ',' ORDER BY ', @leadColPivot) EXEC (@tsql) SET NOCOUNT OFF END GO

2.测试脚本：

CREATE TABLE [dbo].[StudentGrades] ([studentName] [varchar](40) NULL, [courseName] [varchar](40) NULL, [year_study] [int] NULL, [Grade] [int] NULL) ON [PRIMARY] GO INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2017, 90) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2018, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2017, 95) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2018, 96) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r ', N'oracle', 2017, 95) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'oracle', 2018, 96) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2017, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2018, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2017, 99) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2018, 89) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2019, 90) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r ', N'sql', 2017, 76) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'sql', 2018, 80) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'sql', 2019, 100) GO

3.调用执行，结果

EXEC dbo.usp_Dyna_Pivot @unknownValsCol = 'year_study', -- get list of unique values @objNameToPivot = 'StudentGrades', -- table that holds data @aggFuncOfPivot = 'AVG', -- type of operation to perform @aggColOfPivot = 'grade', -- column value for pivot operation @leadColPivot = 'courseName' -- order results by column GO