SQL+SERVER+2005%2B+版本行列转换数据脚本
--创建表CREATE TABLE [dbo].[dduser]( [id] [int] IDENTITY(1,1) NOT NULL, [month] [int] NULL, [username] [nvarchar](36) COLLATE Chinese_PRC_CI_AS NULL, [userDisplay] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [rank] [int] NULL, [score] [int] NULL,PRIMARY KEY CLUSTERED ( [id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]go--插入数据insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'熏衣草' , N'槑党(."".)熏衣草' ,1,14240)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,2,10441)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'ju523756055' , N'槑党(."".)ㄨ.i.a`0' ,9,3286)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'naonaoye' , N'槑党(."".)小坑' ,11,2936)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'zsx841021' , N'槑党(."".)三石:打酱油' ,20,2295)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,35,1465)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,45,1013)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'star_jerry90' , N'槑党(."".)Jerry' , 74,661)insert dduser([month],username,userDisplay,rank,score) values( 1 ,N'abclm' , N'槑党(."".).扫地僧.' ,86,588)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,2,4476)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'naonaoye' , N'槑党(."".)小坑' ,5,3322)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,8,3013)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'beican_shijie' , N'槑党(."".)舉戈' ,17,1791)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,21,1589)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,28,1417)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'zsx841021' , N'槑党(."".)三石:打酱油' ,30,1361)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'ju523756055' , N'槑党(."".)ㄨ.i.a`0' ,40,1002)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'abclm' , N'槑党(."".).扫地僧.' ,45,873)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'pittzhangswing' , N'槑党(."".)【皮特&张】' ,57,687)insert dduser([month],username,userDisplay,rank,score) values( 2 ,N'熏衣草' , N'槑党(."".)熏衣草' ,65,619)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,1,7181)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'beican_shijie' , N'槑党(."".)舉戈' ,2,4643)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'naonaoye' , N'槑党(."".)小坑' ,3,4041)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,8,2933)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,15,1707)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,17,1608)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'zfen12x' , N'槑党(."".)爱生活,爱押宝' ,20,1533)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'chenqi1988126' , N'槑党(."".)阿哥' ,40,880)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'zsx841021' , N'槑党(."".)三石:打酱油' ,44,799)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'abclm' , N'槑党(."".).扫地僧.' ,58,676)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'ju523756055' , N'槑党(."".)ㄨ.i.a`0' ,61,663)insert dduser([month],username,userDisplay,rank,score) values( 3 ,N'pittzhangswing' , N'槑党(."".)【皮特&张】' ,66,622)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,2,4289)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,6,2444)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'naonaoye' , N'槑党(."".)小坑' ,10,1838)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,13,1446)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'zfen12x' , N'槑党(."".)爱生活,爱押宝' ,21,1112)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,26,1001)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'beican_shijie' , N'槑党(."".)舉戈' ,35,859)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'renkuan719' , N'槑党(."".)一缕风' ,68,552)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'pittzhangswing' , N'槑党(."".)【皮特&张】' ,94,428)insert dduser([month],username,userDisplay,rank,score) values( 4 ,N'xuelang1225' , N'槑党(."".)雪狼' ,99,405)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'q2920' , N'槑党(."".)初念' ,1,6398)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,2,5680)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'SCAUSCNU' , N'槑党(."".)阳光女孩' ,4,3175)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'renkuan719' , N'槑党(."".)一缕风' ,5,3001)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'naonaoye' , N'槑党(."".)小坑' ,6,2951)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'zhangxinbin5' , N'槑党(."".)彬-董事长' ,11,1883)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,14,1607)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,18,1290)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'Faith_Ten' , N'槑党(."".)__淡定的弦' ,20,1201)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'beican_shijie' , N'槑党(."".)舉戈' ,25,1023)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,24,979)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'xuelang1225' , N'槑党(."".)雪狼' ,30,897)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'thesnowisflying' , N'槑党(."".)钱袋袋' ,42,745)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'star_jerry90' , N'槑党(."".)Jerry' , 46,702)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'zfen12x' , N'槑党(."".)爱生活,爱押宝' ,53,601)insert dduser([month],username,userDisplay,rank,score) values( 5 ,N'ju523756055' , N'槑党(."".)ㄨ.i.a`0' ,66,425)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,1,7067)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'q2920' , N'槑党(."".)初念' ,3,5417)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'zhangxinbin5' , N'槑党(."".)彬-董事长' ,4,5177)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,5,4104)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'xp447196763' , N'槑党(."".)小飛' ,6,3828)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'renkuan719' , N'槑党(."".)一缕风' ,7,3285)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'naonaoye' , N'槑党(."".)小坑' ,14,1803)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,21,1226)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'abclm' , N'槑党(."".).扫地僧.' ,28,1063)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'thesnowisflying' , N'槑党(."".)钱袋袋' ,36,871)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'RCode' , N'槑党(."".)總監「流年」' ,42,741)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'SCAUSCNU' , N'槑党(."".)阳光女孩' ,49,620)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'熏衣草' , N'槑党(."".)熏衣草' ,52,596)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'q465897859' , N'槑党(."".)--渐行渐远' ,71,461)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'mni2005' , N'槑党(."".)总裁【呆呆】' ,72,443)insert dduser([month],username,userDisplay,rank,score) values( 6 ,N'star_jerry90' , N'槑党(."".)Jerry' , 87,380)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'q2920' , N'槑党(."".)初念' ,1,8191)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'xp447196763' , N'槑党(."".)小飛' ,2,4926)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,4,3910)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'renkuan719' , N'槑党(."".)一缕风' ,5,3496)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,7,2692)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'紫贝壳' , N'槑党(."".)紫贝壳' ,8,2649)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'zhangxinbin5' , N'槑党(."".)彬-董事长' ,11,2378)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'SCAUSCNU' , N'槑党(."".)阳光女孩' ,19,1571)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,39,752)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'naonaoye' , N'槑党(."".)小坑' ,44,699)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'mni2005' , N'槑党(."".)总裁【呆呆】' ,56,575)insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'star_jerry90' , N'槑党(."".)Jerry' , 95,378)insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'tan598121925' , N'槑党(."".)誌Jian.' ,4,3842)insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'xp447196763' , N'槑党(."".)小飛' ,5,3235 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'zhangxinbin5' , N'槑党(."".)彬-董事长' ,7,2858 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'renkuan719' , N'槑党(."".)一缕风' ,9,2517 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'SCAUSCNU' , N'槑党(."".)阳光女孩' ,12,2043 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'q2920' , N'槑党(."".)初念' ,23,1183 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'阿呆哥' , N'槑党(."".)阿呆哥' ,37,660 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'Demon__Hunter' , N'槑党(."".)主席【呆呆】' ,41,611 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'thesnowisflying' , N'槑党(."".)钱袋袋' ,61,499 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'xuelang1225' , N'槑党(."".)雪狼' ,70,440 )insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'q465897859' , N'槑党(."".)--渐行渐远' , 87 ,354)
--SQL SERVER 2005+ 静态行转列示例select row_number() over (order by (select sum(score) from dduser where username=b.username) desc) as 总排名, (select sum(score) from dduser where username=b.username) as 总得分, userDisplay as 昵称, username as 账号, max(isnull('第'+ltrim([1])+'名','')) as [1月], max(isnull('第'+ltrim([2])+'名','')) as [2月], max(isnull('第'+ltrim([3])+'名','')) as [3月], max(isnull('第'+ltrim([4])+'名','')) as [4月], max(isnull('第'+ltrim([5])+'名','')) as [5月], max(isnull('第'+ltrim([6])+'名','')) as [6月], max(isnull('第'+ltrim([7])+'名','')) as [7月], max(isnull('第'+ltrim([8])+'名','')) as [8月]from (select * from dduser) a pivot (max(rank) for [month] in ([1],[2],[3],[4],[5],[6],[7],[8])) bgroup by username,userDisplay order by 总得分 desc--SQL SERVER 2005+ 动态行转列示例declare @sql varchar(max),@colname varchar(max)select @sql = isnull(@sql + '],[' , '') + ltrim([month]) from dduser group by [month]set @sql = '[' + @sql + ']'select @colname= isnull(@colname+',','')+'max(isnull(''第''+ltrim([' +ltrim([month])+'])+''名'','''')) as ['+ltrim([month])+'月]'from dduser group by [month]exec ('select row_number() over (order by (select sum(score) from dduser where username=b.username) desc) as 总排名, (select sum(score) from dduser where username=b.username) as 总得分, userDisplay as 昵称, username as 账号, [email protected]+'from (select * from dduser) a pivot (max(rank) for [month] in ([email protected]+')) bgroup by username,userDisplay order by 总得分 desc')--扩展：如果时间要扩展的话，把month字段改个名字，存储YYYY-MM格式即可存储多个年限的了。