一丶行专列案例【】

1.建立表格

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbgoCREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)insert into tb VALUES ('张三','语文',74)insert into tb VALUES ('张三','数学',83)insert into tb VALUES ('张三','物理',93)insert into tb VALUES ('李四','语文',74)insert into tb VALUES ('李四','数学',84)insert into tb VALUES ('李四','物理',94)goSELECT * FROM tbgo

动态sql

--使用stuff()DECLARE @sql VARCHAR(8000)SET @sql=''  --初始化变量 @sqlSELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值SET @sql= STUFF(@sql,1,1,'')--去掉首个','SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'PRINT @sqlexec(@sql)--或使用isnull()DECLARE @sql VARCHAR(8000)--获得课程集合SELECT @sql= ISNULL(@sql+',','')+课程 FROM tbGROUP BY 课程           SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'exec(@sql)

二丶实战

行转列

 

DECLARE @sql VARCHAR(8000)SET @sql=''  --初始化变量 @sqlSELECT @sql= @sql+',' + '['+Admission_Grade+']' FROM #temp WHERE Admission_Grade>YEAR(GETDATE())-3 GROUP BY Admission_Grade ORDER BY Admission_Grade DESC--变量多值赋值SET @sql= STUFF(@sql,1,1,'')--去掉首个','SET @sql='select * from #temp pivot (max(studentCount) for Admission_Grade in ('+@sql+'))a'PRINT @sqlexec(@sql)

2.

DECLARE @sql VARCHAR(8000)SET @sql=''  --初始化变量 @sqlSELECT @sql= @sql+',' + '['+Admission_Grade+']' FROM #temp WHERE Admission_Grade>YEAR(GETDATE())-3 GROUP BY Admission_Grade ORDER BY Admission_Grade DESC--变量多值赋值SET @sql= STUFF(@sql,1,1,'')--去掉首个','SET @sql = ' select m.* , n.stuCount from(select  * from (select * from #temp) a pivot (max(studentCount) for Admission_Grade in (' + @sql    + ')) b) m,(select Magor_Code,StudyMode,StudyHierarchy,sum(studentCount) as stuCount from #temp WHERE  Admission_Grade>YEAR(GETDATE())-3  group by Magor_Code,StudyMode,StudyHierarchy) nwhere m.Magor_Code= n.Magor_Code AND m.StudyMode=n.StudyMode AND m.StudyHierarchy=n.StudyHierarchy'PRINT @sqlexec(@sql)