Sql Server行转列

--drop table userdatacreate table userdata(uname nvarchar(50),subject nvarchar(50),score int)insert into userdataselect 'Leo','Chinese',80unionselect 'Leo','Englist',60unionselect 'Leo','Math',70unionselect 'Mike','Chinese',90unionselect 'Mike','English',60unionselect 'Jice','Chinese',80unionselect 'Jice','English',50--4种简捷写法供参考。 --SQL2000静态sqlSELECT uname,Chinese = MAX(CASE subject WHEN 'Chinese' THEN score ELSE 0 END), English = MAX(CASE subject WHEN 'English' THEN score ELSE 0 END), Math = MAX(CASE subject WHEN 'Math' THEN score ELSE 0 END) FROM userdata GROUP BY uname ORDER BY uname --SQL2000动态sqlDECLARE @sql NVARCHAR(4000) SET @sql = 'SELECT uname' SELECT @sql = @sql + ', ' + a.subject + ' = MAX(CASE subject WHEN ''' + a.subject + ''' THEN score ELSE 0 END)' FROM (SELECT DISTINCT subject FROM userdata) a ORDER BY subjectSET @sql = @sql + ' FROM userdata GROUP BY uname ORDER BY uname' EXEC(@sql) --SQL2005静态sqlSELECT * FROM (SELECT * FROM userdata) a PIVOT (MAX(score) FOR subject IN (Chinese, English, Math)) b --SQL2005动态sqlDECLARE @sql NVARCHAR(4000) SELECT @sql = ISNULL(@sql + ',' , '') + subject FROM userdata GROUP BY subject EXEC ('SELECT * FROM (SELECT * FROM userdata) a PIVOT (MAX(score) FOR subject IN (' + @sql + ')) b')