当前位置: 代码迷 >> SQL >> SQL+SERVER+2005%2B+版本队列转换数据脚本
  详细解决方案

SQL+SERVER+2005%2B+版本队列转换数据脚本

热度:13   发布时间:2016-05-05 13:34:09.0
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格式即可存储多个年限的了。


  相关解决方案