行列转换,交叉表 (统计用, 表的值转换成字段)

标签: 行列 交叉 统计 | 发表时间:2011-10-20 14:30 | 作者:※森林小居※ Bloger
出处:http://www.cnblogs.com/

这段时间在看SAAS方面的书籍时发现一个关于数据处理的问题。里面讲到一种扩展性非常好的方法。但一直到今天终于在网上找到了。只可惜现在还在探索,只是找到一个例子而已。我在这里把共享希望对大家有用。下面的语句在sql server 2005中我测试过没有问题。

1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
想变成
姓名      语文      数学      英语
张三      80        86        75
李四      78        85        78

declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)
drop table test
eg2:
有表A,
id pid
1      1
1      2
1      3
2      1
2      2
3      1
如何化成表B:
id pid
     1     1,2,3
     2     1,2
     3     1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!

--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A

2:
/***********        行转列      *****************/
测试:
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)
declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')
a           
-----------
15
9
1
0
1
2
2
0
===============================================================================
                以下个带出的问题:
===============================================================================

3.将结果矩阵转置
if      exists      (select      *      from      sysobjects      where      id      =      object_id('proc_sky_blue')      and      xtype      ='P')
drop      proc      proc_sky_blue
go
create      proc      proc_sky_blue      (@tablename      varchar(200))
as
begin
       set      nocount      on
       declare      @col      nvarchar(256)
       declare      @makesql      nvarchar(4000)
       declare      @insertsql      nvarchar(4000)
       declare      @caculatesql      nvarchar(400)
       declare      @count      int
       declare      @i      int
       create      table      #tmp      (colname      nvarchar(20))
       select      @caculatesql      =      'select      @count=count(1)      from      '      +      @tablename
       exec      sp_executesql      @caculatesql,      N'@count      int      output',@count      output
       if      @count      >=1024
       begin
           raiserror('表的行数太多了,我转不了',16,1)
       end
       else
       begin
           select      @i=0
           while      @count      >0
           begin
               select      @i=@i+1
               select      @makesql      =      'alter      table      #tmp      add      col'+convert(varchar(20),@i)+'      int'
               exec(@makesql)
               select      @count=@count-1         
           end
           declare      my_cursor      cursor      for   
           select      name      from      syscolumns      where      id=object_id(@tablename)      order      by      colid
           open      my_cursor
           fetch      next      from      my_cursor      into      @col
           while      @@fetch_status      =      0
           begin
               select      @makesql      ='select      @insertsql=      @insertsql      +      convert(varchar(4),'+@col+')      +'',''      from      '      +@tablename
               select      @insertsql      =N'insert      #tmp      values      ('''+@col+      ''','
               execute      sp_executesql      @makesql,N'@insertsql      nvarchar(4000)      output'      ,@insertsql      output
               select      @insertsql      =      left(@insertsql,len(@insertsql)-1)      +')'
               exec(@insertsql)
               fetch      next      from      my_cursor      into      @col
           end
           close      my_cursor
           deallocate      my_cursor
           select      *      from      #tmp
           set      nocount      off
       end
end

go
----------------分析
declare      @tablename      varchar(200)
set      @tablename='table1'
begin
       set      nocount      on
       declare      @col      nvarchar(256)
       declare      @makesql      nvarchar(4000)
       declare      @insertsql      nvarchar(4000)
       declare      @caculatesql      nvarchar(400)
       declare      @count      int
       declare      @i      int
       create      table      #tmp      (colname      nvarchar(20))
       select      @caculatesql      =      'select      @count=count(1)      from      '      +      @tablename
       exec      sp_executesql      @caculatesql,      N'@count      int      output',@count      output
       if      @count      >=1024
       begin
           raiserror('表的行数太多了,我转不了',16,1)
       end
       else
       begin
           select      @i=0
           while      @count      >0
           begin
               select      @i=@i+1
               select      @makesql      =      'alter      table      #tmp      add      col'+convert(varchar(20),@i)+'      int'
               exec(@makesql)
               select      @count=@count-1         
           end
           declare      my_cursor      cursor      for   
           select      name      from      syscolumns      where      id=object_id(@tablename)      order      by      colid
           open      my_cursor
           fetch      next      from      my_cursor      into      @col
           while      @@fetch_status      =      0
           begin
               select      @makesql      ='select      @insertsql=      @insertsql      +      convert(varchar(4),'+@col+')      +'',''      from      '      +@tablename
               select      @insertsql      =N'insert      #tmp      values      ('''+@col+      ''','
               execute      sp_executesql      @makesql,N'@insertsql      nvarchar(4000)      output'      ,@insertsql      output
               select      @insertsql      =      left(@insertsql,len(@insertsql)-1)      +')'
               select      @insertsql
               --exec(@insertsql)
               fetch      next      from      my_cursor      into      @col
           end
           close      my_cursor
           deallocate      my_cursor
           select      *      from      #tmp
           set      nocount      off
           drop      table      #tmp

作者: ※森林小居※ 发表于 2011-10-20 14:30 原文链接

评论: 0 查看评论 发表评论


最新新闻:
· PC厂商担忧谷歌削减对平板电脑资源投入(2011-10-20 23:07)
· 诺基亚今年或开售多款Windows Phone手机(2011-10-20 22:27)
· 索爱计划明年将Xperia系列手机升至Android 4.0(2011-10-20 22:25)
· 腾讯移动安全实验室发布行业首份伪装病毒研究报告(2011-10-20 22:11)
· AT&T称其激活的iPhone 4S已超过100万部(2011-10-20 22:07)

编辑推荐:最老程序员创业札记:全文检索、数据挖掘、推荐引擎应用50

网站导航:博客园首页  我的园子  新闻  闪存  小组  博问  知识库

相关 [行列 交叉 统计] 推荐:

行列转换,交叉表 (统计用, 表的值转换成字段)

- Bloger - 博客园-首页原创精华区
这段时间在看SAAS方面的书籍时发现一个关于数据处理的问题. 里面讲到一种扩展性非常好的方法. 只可惜现在还在探索,只是找到一个例子而已. 下面的语句在sql server 2005中我测试过没有问题. insert test values('张三','语文',80). insert test values('张三','数学',86).

交叉关联的时代

- 书皮 - 互联网的那点事...
记得有天晚上尝试了ifttt (ifttt.com)这个网站之后,兴奋地久久不能入睡. 这是一个开放的空间,本身没有任何内容,它所做的就是通过调用其他开放型产品的API(例如Facebook、Gmail、Feed等),构建起产品之前的潜在关联. 你通过创建if(触发条件)和then(触发反馈)来实现一些新鲜有趣的服务推送.

向上营销、交叉营销与关联推荐

- - 互联网分析
我们会发现很多网站都具备了内容推荐的功能,不仅是像B2C电子商务类的卓越的图书推荐,也包括兴趣类网站像豆瓣的豆瓣猜等. 这类功能无疑在帮助用户发现需求,促进商品购买和服务应用方面起到了显著性的效果. 其实跟网站数据分析不无相关,我们可以来简单看一下它的原理和实现. 关联推荐在营销上被分为两类:. 向上营销 (Up Marketing):根据既有客户过去的消费喜好,提供更高价值或者其他用以加强其原有功能或者用途的产品或服务.

在数据与设计的交叉路口,Facebook时间线诞生了

- Bourne - 36氪
在今天的Facebook f8大会上,CEO扎克伯格向人们展示了他们巨大的变化:时间线,这是个人档案页面同重生,看起来很棒. 之后,Facebook首席产品官Chris Cox登台讲述时间线背后的故事. Cox主要讲了2个人:Nicholas Felton和Sam Lessin. Cox用一句诗来描述Felton的历史:“14页.

Windows 7 与 Windows XP 黄金交叉,Vista 躲在角落画圈圈

- kxxoling - Engadget 中国版
跟据 StatCounter 的数据,Windows 7 花了两年的时间从无到现在取代了 Windows XP,成为操作系统界的一哥. 最新的数据 Windows XP 的市占率为 38%,略输 Windows 7 的 40%,而 Vista 则是以 11% 远远的落后它的两兄弟. 前五名的剩下两位分别为成长缓慢的 MacOSX,市场占有率 7%,以及趴在地板上的 Linux,市场占有率 0.82%.

Microsoft 与 Casio 签署 Linux 专利交叉授权协议,Casio 真的有用到吗?

- pipitu - Engadget 中国版
先帮大家做好心里准备,如果你碰巧正在制作运行 Linux 系统的设备,那么未来接到来自 Microsoft 的贴心来电机率将越来越高(当然不会是送你手机 笔电的那种来电),而这件事情才刚发生在 Casio 身上. 该公司也加入了 TomTom 和 Amazon 的行列,含泪签署了与 Microsoft 的 Linux 系统专利交叉许可协议,其中涵盖了 Casio 的多项侵犯专利装置的范围.

Microsoft 與 Casio 簽署 Linux 專利交叉授權協議,Casio 真的有用到嗎?

- Adam - Engadget 中文版
先幫大家做好心裡準備,如果你碰巧正在製作運行 Linux 系統的裝置,那麼未來接到來自 Microsoft 的貼心來電機率將越來越高(當然不會是送你手機 筆電的那種來電),而這件事情才剛發生在 Casio 身上. 該公司也加入了 TomTom 和 Amazon 的行列,含淚簽署了與 Microsoft 的 Linux 系統專利交叉授權合約,其中涵蓋了 Casio 的多項侵犯專利裝置的範圍.

Google+统计报告

- pestwave - 36氪
Google+推出有一段时间了,用户性别比例如何呢. find people on plus对947996名Google+用户进行了统计,按照性别、地理位置、职位进行了分类,甚至还对来自Facebook和Google的员工进行了统计. 男:698,703 (73.70%). 女:234,504 (24.74%).

足球的统计

- Race forward! - 学而时嘻之
《足球的逻辑》这篇得到了不少有益的批评,以至于我很想写一个修改版,不过更有意思的做法是接着另写一篇. 更重要的是,这回终于可以提供一点数据支持了. 足球比赛的一个一般规律是下半场比上半场容易进球. 在以下几个大赛中,上下半场的进球比率分别是:. 98年世界杯:上半场39.2%,下半场60.8%. 02年世界杯:上半场41%,下半场59%.