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

于 2012-09-06 14:50:24 发布
阅读量4.8k 收藏
点赞数
分类专栏： SQL 文章标签： sql server insert null pivot 生活
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chinaboykai/article/details/7950848
版权 
--创建表
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])) 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 (
'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格式即可存储多个年限的了。


天堂镇的幽灵
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值