SQL行列转换，交叉表 （统计用，支持动态类别的SQL）

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 testeg2:
有表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
       end
end

转载于:https://www.cnblogs.com/Madream/articles/1044661.html