## 行列转换，交叉表 （统计用， 表的值转换成字段）

2011-10-20 14:30  ※森林小居※  阅读(1954)  评论(0编辑  收藏  举报

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)

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:

id pid
1      1
1      2
1      3
2      1
2      2
3      1

id pid
1     1,2,3
2     1,2
3     1

--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