sql server和oracle 行列转换

 1. 行列转换--普通

假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82

想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82

测试: --by yanleigis email:[e-mail]

create table CJ
(
 id int IDENTITY(1,1) not null primary key, --自增加1
 Name  varchar(8),
 Subject  varchar(8),
 Result float
)

alter table cj alter column result numeric(8,1) --设置小数位数
insert into CJ (Name,Subject,Result) values ('张三','语文',80)
insert into CJ (Name,Subject,Result) values ('张三', '数学', 90)
insert into CJ (Name,Subject,Result) values ('张三','物理', 85)
insert into CJ (Name,Subject,Result) values ('李四','语文', 85)
insert into CJ (Name,Subject,Result) values ('李四','数学', 92)
insert into CJ (Name,Subject,Result) values ('李四', '物理', 82)

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from cj group by name'
PRINT @sql --打印变量的值,注释两种方法--,/**/
exec(@sql)
select Name,sum(case Subject when '数学' then Result end) [数学],sum(case Subject when '物理' then Result end) [物理],sum(case Subject when '语文' then Result end) [语文] from cj group by name

2. 行列转换--合并

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

创建一个合并的函数
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

测试:

create table 表A
(
id int,
pid int)

insert into 表A values (1,1);
insert into 表A values (1,2);
insert into 表A values (1,3);
insert into 表A values (2,1);
insert into 表A values (2,2);
insert into 表A values (3,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

来自：http://www.cnblogs.com/stu-acer/archive/2006/05/16/401445.html

下面的是Oracle行列转换实战(转载)

                     行列转换实例
                  表ttt有三个字段
                  seq  --序列
                  jcxm --检查项目
                  zhi  --值

                  数据分别如下：
                  seq　　　jcxm　　     zhi
                  -------      --------          --------
                  11　　　　　1　　　　0.50
                  11　　　　　2　　　　0.21
                  11　　　　　3　　　　0.25
                  12　　　　　1　　　　0.24
                  12　　　　　2　　　　0.30
                  12　　　　　3　　　　0.22                             

                  实现功能
                  创建视图时移动行值为列值


                  create view v_view1
                  as
                  select seq,
                         sum(decode(jcxm,1, zhi)) 检测项目1,
                         sum(decode(jcxm,2, zhi)) 检测项目2, 
                         sum(decode(jcxm,3, zhi)) 检测项目3 
                  from ttt
                  group by seq;

                  序号 检测项目1　　检测项目2　　检测项目3
                  11　　　　　0.50　　　　0.21　　　　　0.25
                  12　　　　　0.24　　　　0.30　　　　　0.22

 

                  技巧：
                  用THEN中的0和1来进行统计（SUM）

                  jcxm   zhi
                  ----   ----
                  a           1
                  b           1
                  a           3
                  d           2
                  e           4
                  f           5
                  a           5
                  d           3
                  d           6
                  b           5
                  c           4
                  b           3
                  求他的zhi既是1，也是3，也是5的jcxm
                  方法一
                  select jcxm
                  from ttt
                  group by jcxm
                  having sum(decode(zhi,1,-1,3,-1,5,-1,0)) = -3
                  方法二
                  select jcxm from ttt 
                  group by jcxm having (sign(sum(decode(zhi,1,-1,0)))+
                  sign(sum(decode(zhi,3,-1,0)))+sign(sum(decode(zhi,5,-1,0)))<=-3);

                  ----------
                  a
                  b
                  说明：
                  sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
                  所以可以用sign和decode来完成比较字段大小来区某个字段
                  select decode(sign(字段1-字段2),-1,字段3,字段4) from dual;

                  sign是一个对于写分析SQL有很强大的功能
                  下面我对sign进行一些总结：
                  但属性student取0和1以外的值，或者student取两个以上的标法值，问题就不会这么简单了
                  解决办法就是特征函数（abs(),sign()）

                  常用的特征算法
                  ［A＝B］＝1－abs（sign（A－B））
                  ［A！＝B］＝abs（sign（A－B）） 
                  ［A<B］＝1－sign（1＋sign（A－B）） 
                  不能用-sign（A－B）：因为如果不满足A<b则返回-1，而不是0，这样就不能用在字段选择上了
                  ［A<＝B］＝sign(1－sign（A－B））
                  ［A>B］＝1－sign（1－sign（A－B））
                  ［A>＝B］＝sign(1＋sign（A－B））)
                  ［NOTα］＝1－d ［α］
                  ［αANDb ］＝d ［α］*d ［b ］ (6)
                  ［αOR b ］＝sign（d ［α］＋d [b ]）

                  例如：
                  A<B                         Decode( Sign(A-B), -1, 1, 0 )      
                     
                  A<=B                         Decode( Sign(A-B), 1, 0, 1 )      
                     
                  A>B                         Decode( Sign(A-B), 1, 1, 0 )       
                    
                  A>=B                         Decode( Sign(A-B), -1, 0, 1 )     
                      
                  A=B                         Decode( A, B, 1, 0 )         
                  A between B and C      Decode( Sign(A-B), -1, 0, 
                  Decode(Sign(A-C), 1, 0, 1 ))         
                  A is null                       Decode(A,null,1,0)         
                  A is not null                 Decode(A,null,0,1)         A in 
                  (B1,B2,,Bn)  Decode(A,B1,1,B2,1,,Bn,1,0)         
                  nor LogA                    Decode( LogA, 0, 1, 0 )            
                    (1-Sign(LogA)) 
                  LogA and LogB            LogA * LogB 
                  LogA or LogB              LogA + LogB 
                  LogA xor LogB            Decode(Sign(LogA),Sign(LogB),0,1)    
                  Mod(Sign(LogA),Sign(LogB),2


                  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

                  [NextPage]
                  另外一个关于成绩的分析例子

                  SELECT
                  SUM(CASE WHEN cj <60 THEN 1 ELSE 0 END) as "not passed",
                  SUM(CASE WHEN cj BETWEEN 60 AND 79 THEN 1 ELSE 0 END) as 
                  "passed",
                  SUM(CASE WHEN cj BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as 
                  "good",
                  SUM(CASE WHEN cj >=90 THEN 1 ELSE 0 END) as "Excellent"
                  FROM cjtable;

                  decode用法2
                  表、视图结构转化
                  现有一个商品销售表sale，表结构为：
                  month　　　 char(6)　　　　　 --月份
                  sell　　　　number(10,2)　　　 --月销售金额

                  现有数据为：
                  200001　　1000
                  200002　　1100
                  200003　　1200
                  200004　　1300
                  200005　　1400
                  200006　　1500
                  200007　　1600
                  200101　　1100
                  200202　　1200
                  200301　　1300

                  想要转化为以下结构的数据：
                  year　　　char(4)　　　　　     --年份
                  ------------   ---------------------         
                  -------------------
                  month1　　number(10,2)　　　--1月销售金额
                  month2　　number(10,2)　　　--2月销售金额
                  month3　　number(10,2)　　　--3月销售金额
                  month4　　number(10,2)　　　--4月销售金额
                  month5　　number(10,2)　　　--5月销售金额
                  month6　　number(10,2)　　　--6月销售金额
                  month7　　number(10,2)　　　--7月销售金额
                  month8　　number(10,2)　　　--8月销售金额
                  month9　　number(10,2)　　　--9月销售金额
                  month10　 number(10,2)　　   --10月销售金额
                  month11　 number(10,2)　　   --11月销售金额
                  month12　 number(10,2)　　   --12月销售金额

                  结构转化的SQL语句为：
                  create or replace view
                  v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
                  as
                  　　　　select 
                  　　　　substrb(month,1,4),
                  　　　　sum(decode(substrb(month,5,2),'01',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'02',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'03',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'04',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'05',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'06',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'07',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'08',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'09',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'10',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'11',sell,0)),
                  　　　　sum(decode(substrb(month,5,2),'12',sell,0))
                  　　　　from sale
                  　　　　group by substrb(month,1,4);

                  体会：要用decode /group by/ order by/sign/sum来实现不同报表的生成 
                  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                  CASE应用

                  1        1        部门a        800        男
                  2        2        部门b        900        女
                  3        3        部门a        400        男
                  4        4        部门d        1400        女
                  5        5        部门e        1200        男
                  6        6        部门f        500        男
                  7        7        部门a        300        女
                  8        8        部门d        1000        男
                  9        9        部门d        1230        女
                  10        10        部门b        2000        女
                  11        11        部门c        2000        男
                  12        12        部门b        1200        男

                    SELECT jcxm as 部门,COUNT(seq) as 人数,
                      SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
                            SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
                      SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 
                  小于800元,
                      SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)                    
                  /**//*用*来实现<和>功能*/
                           WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
                           WHEN 800  THEN 1 ELSE 0 END)) as 从800至999,         
                  /**//*注意别名不能以数字开头*/
                      SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
                           WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
                           WHEN 1000 THEN 1 ELSE 0 END)) as 从1000元至1199元,
                      SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
                      +(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大于1200元
                  FroM ttt 
                  GROUP BY jcxm

                  部门名 人数    男       女   小于800元 从800至999 从1000元至1199元   大于1200元
                  部门a        3        2        1        2        1           0   
                                             0
                  部门b        3        1        2        0        1           0   
                                             2
                  部门c        1        1        0        0        0           0   
                                            1
                  部门d        3        1        2        0        0           1   
                                            2
                  部门e        1        1        0        0        0             0 
                                              1
                  部门f        1        1        0        1        0           0   
                                            0