-行列互换/*--有表 indust 200301 200302 200303 ---------- ---------- ---------- ---------- a 111 222 333 b 444 555 666 c 777 888 999 d 789 910 012 --要求得到结果 日期 a b c d ------ ---- ---- ---- ---- 200301 111 444 777 789 200302 222 555 888 910 200303 333 666 999 012 --*/--创建测试表 create table test(indust varchar(10) ,[200301] varchar(10) ,[200302] varchar(10) ,[200303] varchar(10)) insert test select 'a','111','222','333' union all select 'b','444','555','666' union all select 'c','777','888','999' union all select 'd','789','910','012' go--数据处理 declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000) select @f1='',@f2='',@f3='' select @f1=@f1+',['+indust+']='''+[200301]+'''' ,@f2=@f2+','''+[200302]+'''' ,@f3=@f3+','''+[200303]+'''' from test exec('select 日期=''200301'''+@f1 +' union all select ''200302'''+@f2 +' union all select ''200303'''+@f3) go --删除测试表 select * from test drop table test/*--测试结果 日期 a b c d ------ ---- ---- ---- ---- 200301 111 444 777 789 200302 222 555 888 910 200303 333 666 999 012 --*/
select id,sum(decode(col1,'A',col2,'')) col1,sum(decode(col1,'B',col2,'')) col2 from tablea group by id
create table tablea ( id varchar(20), col1 varchar(20), col2 varchar(20) ) insert into tablea(id,col1,col2) select 101,'A',555 from dual union all select 101,'B',666 from dual union all select 102,'A',777 from dual union all select 103,'B',888 from dual commit; select * from tablea select id,sum(decode(col1,'A',col2,'')) col1,sum(decode(col1,'B',col2,'')) col2 from tablea group by id
indust 200301 200302 200303
---------- ---------- ---------- ----------
a 111 222 333
b 444 555 666
c 777 888 999
d 789 910 012
--要求得到结果
日期 a b c d
------ ---- ---- ---- ----
200301 111 444 777 789
200302 222 555 888 910
200303 333 666 999 012
--*/--创建测试表
create table test(indust varchar(10)
,[200301] varchar(10)
,[200302] varchar(10)
,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666'
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go--数据处理
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
,@f2=@f2+','''+[200302]+''''
,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
+' union all select ''200302'''+@f2
+' union all select ''200303'''+@f3)
go
--删除测试表
select * from test
drop table test/*--测试结果
日期 a b c d
------ ---- ---- ---- ----
200301 111 444 777 789
200302 222 555 888 910
200303 333 666 999 012
--*/
from tablea
group by id
(
id varchar(20),
col1 varchar(20),
col2 varchar(20)
)
insert into tablea(id,col1,col2)
select 101,'A',555 from dual
union all
select 101,'B',666 from dual
union all
select 102,'A',777 from dual
union all
select 103,'B',888 from dual
commit; select * from tablea select id,sum(decode(col1,'A',col2,'')) col1,sum(decode(col1,'B',col2,'')) col2
from tablea
group by id