在CSDN社区 MS-SQLSERVER板块,关于行转列的问题,层出不穷,之前dawugui,roy_88等高手都多次整理过,我就不再整理了,只是给出一个示例,以供大家参考。
槑党是CSDN水源2012年新出现的,这里对2012年1-8月的相关数据,简单的统计一下。
数据采集于下面地址:
统计只是在扩充话题板块TOP100名单中出现的用户,以昵称中出现槑党字样的为样本。
数据显示槑党在2012年1-8月期间,TOP100中有的相关数据共得216951分。
行转列后的统计结果为:
数据脚本我已经上传到了下载频道,有感兴趣的朋友,可以去免费下载。
地址: 相关数据脚本下载地址(含创建表的脚本和插入数据的脚本,无需积分)
行转列代码如下:
- --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格式即可存储多个年限的了。