SQL2000行列倒置的实战例子脚本

use master
go
create database upbs_test
go
use upbs_test
go
create table tb_goods
(
 id int identity(1,1) primary key,
 oid varchar(20),
 cnt  int,
 price money,
 tp varchar(20)
)
go

insert into tb_goods values('G1',2,5,'Ta')
insert into tb_goods values('G1',2,5,'Tb')
insert into tb_goods values('G1',1,7,'Tc')
insert into tb_goods values('G2',2,2,'Ta')
insert into tb_goods values('G2',3,8,'Tb')
insert into tb_goods values('G2',1,6,'Tc')
insert into tb_goods values('G2',1,9,'Ta')
insert into tb_goods values('G2',3,1,'Tb')
insert into tb_goods values('G3',1,9,'Tc')
insert into tb_goods values('G3',6,4,'Ta')
insert into tb_goods values('G5',8,3,'Tb')
insert into tb_goods values('G5',2,1,'Tc')
insert into tb_goods values('G5',9,6,'Ta')
insert into tb_goods values('G6',3,3,'Tb')
insert into tb_goods values('G6',2,5,'Tc')

insert into tb_goods values('G1',8,3,'Ta')
insert into tb_goods values('G2',2,1,'Tb')
insert into tb_goods values('G3',9,6,'Tc')
insert into tb_goods values('G4',3,3,'Ta')
insert into tb_goods values('G5',2,5,'Tb')
insert into tb_goods values('G6',2,5,'Tc')

insert into tb_goods values('G7',3,3,'Ta')
insert into tb_goods values('G7',2,5,'Tb')

insert into tb_goods values('G8',3,3,'Tc')

----------------------------------------------------------------------------------------------------------------------------------

 ---first step
 select oid,
  (case tp when 'ta' then isnull(cp, 0) end) as ta,
  (case tp when 'tb' then isnull(cp, 0) end) as tb,
  (case tp when 'tc' then isnull(cp, 0) end) as tc
 from viw_g

 

 ---second step
 select oid,
  sum(ta) as ta,
  sum(tb) as tb,
  sum(tc) as tc
 from
  (
   select oid,
   (case tp when 'ta' then isnull(cp, 0) end) as ta,
   (case tp when 'tb' then isnull(cp, 0) end) as tb,
   (case tp when 'tc' then isnull(cp, 0) end) as tc
   from (select oid,sum(cnt*price) cp,tp from tb_goods group by oid,tp) as s
  ) as b
 group by oid

-----------------------------------------------------------------------------------------------------------

利用游标动态拼接SQL语句

declare myCur cursor for select distinct tp from tb_goods
declare 
 @tp varchar(20),
 @sum varchar(300),
 @case varchar(500),
 @sql nvarchar(1000)
set @sum = ''
set @case = ''
set @sql = ''
open myCur
 fetch next from myCur into @tp
while @@fetch_status =0
begin
 print @tp
 set @sum = @sum + 'sum(' + @tp + ') as '  + @tp +','
 set @case = @case + '(case tp when '''  + @tp + ''' then isnull(cp,0) end) as ' + @tp +','
 fetch next from myCur into @tp
end
close myCur
deallocate myCur

if right(@case,1) = ','
 set @case = stuff(@case, len(@case), 1, ' from (select oid,sum(cnt*price) cp,tp from tb_goods group by oid,tp) as s')

if right(@sum,1) = ','
 set @sum = stuff(@sum,len(@sum),1,'')

set @sql = 'select oid, ' + @sum + ' from ( select oid, ' + @case + ') as b group by oid'

execute sp_executesql @sql

【execute sp_executesql @sql-->也可写成 exec(@sql) 】

可以参考一下http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx