select 1 [序号],'张三' [姓名],85 [语文],85 [数学],85 [英语],85 [历史],85 [地理],85 [生物],85 [化学] INTO T UNION ALL
select 2 [序号],'李四' [姓名],85 [语文],85 [数学],85 [英语],85 [历史],85 [地理],85 [生物],85 [化学] UNION ALL
select 3 [序号],'王五' [姓名],85 [语文],85 [数学],85 [英语],85 [历史],85 [地理],85 [生物],85 [化学] UNION ALL
select 4 [序号],'赵六' [姓名],85 [语文],85 [数学],85 [英语],85 [历史],85 [地理],85 [生物],85 [化学]
--读取系统表取列
DECLARE @SQL VARCHAR(8000),@str VARCHAR(1000)
SET @str=(SELECT STUFF((SELECT ',['+name+']' FROM SYS.COLUMNS WHERE OBJECT_ID IN(SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME='T')AND column_id>2 FOR XML PATH('')),1,1,''))
SET @SQL=';SELECT * INTO #T1 FROM T UNPIVOT (VALUE FOR [科目] IN('+@str+'))A
DECLARE @str1 VARCHAR(1000),@SQL1 VARCHAR(8000)
SET @str1=(SELECT STUFF((SELECT '',[''+[姓名]+'']'' FROM (SELECT distinct[姓名] FROM #T1)A FOR XML PATH('''')),1,1,''''))
SET @SQL1=''SELECT [科目],''+@str1+'' FROM (SELECT [姓名],[科目],[value] FROM #T1)B PIVOT(MAX([VALUE]) FOR [姓名] in(''+@str1+''))A''
EXEC(@SQL1)
DROP TABLE #T1
'
PRINT @SQL
EXEC(@SQL)
DROP TABLE T