指引网

当前位置: 主页 > 数据库 > SQLServer >

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

来源:网络 作者:佚名 点击: 时间:2018-03-14 08:34
[摘要] SQL+SERVER+2005%2B+版本行列转换数据脚本[sql]--创建表 CREATE TABLE [dbo].[dduser]( [id] [int] IDENTITY(1,1) NOT NULL, [month] [int] NULL, www.2cto.com [username] [nvarch...

SQL+SERVER+2005%2B+版本行列转换数据脚本   [sql]  --创建表   CREATE TABLE [dbo].[dduser](       [id] [int] IDENTITY(1,1) NOT NULL,       [month] [int] NULL,    www.2cto.com       [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)    www.2cto.com   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)    www.2cto.com   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)    www.2cto.com   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)    www.2cto.com   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)    www.2cto.com   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]   www.2cto.com   --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     www.2cto.com   (select * from dduser) a    pivot (max(rank) for [month] in ([1],[2],[3],[4],[5],[6],[7],[8])) b   group 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 (    www.2cto.com   '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 账号,       '+@colname+'   from (select * from dduser) a    pivot (max(rank) for [month] in ('+@sql+')) b   group by username,userDisplay order by 总得分 desc')  
  --扩展:如果时间要扩展的话,把month字段改个名字,存储YYYY-MM格式即可存储多个年限的了。
 
------分隔线----------------------------