行列转换/矩阵转换

83 阅读 0 评论 55 点赞

我是靠谱客的博主单纯钢铁侠，最近开发中收集的这篇文章主要介绍行列转换/矩阵转换，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、行列转换。
1、纵转换为横。
有表：test(name char(10),km char(10),cj int)
name  km  cj
----------------------------------------------
张三  语文  80
张三  数学  86
张三  英语  75
李四  语文  78
李四  数学  85
李四  英语  78

要求以横向格式显示，即：
想变成

姓名   语文   数学   英语
----------------------------
张三   80     86     75
李四   78     85     78

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)

方法一：
select 姓名=name,语文=sum(case km when '语文' then cj else 0 end),数学=sum(case km when '数学' then cj else 0 end)
,英语=sum(case km when '英语' then cj else 0 end)
from test group by name
解析：此方法简单易懂，但列名与列数固定，不够灵活。
方法二：
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

解析：此方法结构稍复杂，但通用性好，在列数不固定时能体现出它的优点。

二、横转换为纵

有表 unknown
name a c b d
Tom 1 2 3 4
Sun 1 2 3 4
要求以纵向格式显示
name km value
tom a 1
tom c 2
tom b 3
tom d 4
sun a 1
sun c 2
sun b 3
sun d 4
test:
create table unknown(name char(4),a int,c int,b int,d int)
insert unknown(name,a,c,b,d)
select 'a',1,2,3,4
union all select 'b',5,6,7,8
union all select 'c',9,10,11,12
union all select 'd',13,14,15,16
1.
select * from (
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown
)tmp
order by name,item

2.排序按列的顺序
select * from (
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown
)tmp
order by name,charindex(item,'acbd')
或使用系统表
select view1.* from
(
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown
) view1 ,
syscolumns s
where view1.item *= s.name and id=object_id('unknown')
order by view1.name,s.colid
或者
select a.name,item=b.name,value=(case b.name when 'a' then a when 'b' then b when 'c' then c else d end)
from unknown a,syscolumns b where b.id=object_id('unknown') and b.name<>'name' order by a.name,b.colid
当列比较多时可以这样
declare @sql varchar(8000)
set @sql = 'select a.name,item=b.name,value=sum(case b.name '
select @sql = @sql + ' when '''+name+''' then '+name
from (select distinct name from syscolumns where id=object_id('unknown') and name<>'name') as a
select @sql = @sql+' end) from unknown a,syscolumns b where id=object_id(''unknown'') and b.name<>''name'' group by a.name,b.name,b.colid order by a.name,b.colid'
select @sql
exec(@sql)

二、矩阵转置
有表 unknown
nam a c b d
--------------------------------------
Tom 1 2 3 4
Sun 5 6 7 8
mon 9 10 11 12
das 13 14 15 16
hor 17 18 19 20

要求以纵向格式显示
name col1 col2 col3 col4 col5
----------------------------------------------
nam tom sun mon das hor
a 1 5 9 13 17
c 2 6 10 14 18
b 3 7 11 15 19
d 4 8 12 16 20
方法一：使用循环。
create table test(nam varchar(4),a int,c int,b int,d int)
insert test(nam,a,c,b,d)
    select 'Tom',     1,     2,     3,     4
union all select 'Sun' ,    5 ,    6 ,    7 ,    8
union all select 'mon' ,   9 ,   10 ,    11,     12
union all select 'das'   , 13 ,   14 ,    15,     16
union all select 'hor'    , 17   , 18 ,   19 ,    20

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

exec proc_sky_blue 'test'

drop table test
drop proc proc_sky_blue
方法二：

--测试数据

create table test(nam varchar(4),a int,c int,b int,d int)
insert test(nam,a,c,b,d)
    select 'Tom',     1,     2,     3,     4
union all select 'Sun' ,    5 ,    6 ,    7 ,    8
union all select 'mon' ,   9 ,   10 ,    11,     12
union all select 'das'   , 13 ,   14 ,    15,     16
union all select 'hor'    , 17   , 18 ,   19 ,    20
union all select 'Jun' ,   9 ,   10 ,    11,     12
union all select 'Feb'   , 13 ,   14 ,    15,     16
union all select 'Mar'    , 17   , 18 ,   19 ,    20
union all select 'Apr' ,   9 ,   10 ,    11,     12
union all select 'May'   , 13 ,   14 ,    15,     16
union all select 'Jun'    , 17   , 18 ,   19 ,    20
union all select 'Jul' ,   9 ,   10 ,    11,     12
union all select 'Aug'   , 13 ,   14 ,    15,     16
union all select 'Sep'    , 17   , 18 ,   19 ,    20
union all select 'Oct' ,   9 ,   10 ,    11,     12
union all select 'Nov'   , 13 ,   14 ,    15,     16
union all select 'Dec'    , 17   , 18 ,   19 ,    20

--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(800)'
,@s2=@s2+',@'+@i+'='''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+nam+'']=''+cast(['+name+'] as varchar) from test'
,@s4=@s4+',@'+@i+'=''select ''+substring(@'+@i+',2,8000)'
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('test')=id and colid<>1

select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
select @s1,@s2,@s3,@s4,@s5

exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go

--删除测试表
drop table test

解读：N列数据用N-1个变量来保存N-1列的数据列表，其中第一列作为字段。
此方法灵活地应用了动态语句的特点，其大致思路是一次取得一列数据的值。
即展开后s3的值为：
select @0=@0+',['+nam+']='+cast([a] as varchar) from test
select @1=@1+',['+nam+']='+cast([c] as varchar) from test
select @2=@2+',['+nam+']='+cast([b] as varchar) from test
select @3=@3+',['+nam+']='+cast([d] as varchar) from test
缺点：受被转换的数据行行数限制，即上面的@0,@1,@2,@3最大只能容纳8000个字符
转换后的行不能超过1024行，这是数据库的限制。