mssql中交叉表的建立

CREATE TABLE test ([no] [int] NOT NULL ,[accno] [nvarchar] (255) COLLATE Latin1_General_BIN NOT NULL ,[qty] [float] NULL ) ON [PRIMARY]
GO

INSERT INTO test VALUES(1,'2100-55020100',100)
INSERT INTO test VALUES(1,'2100-55020200',200)
INSERT INTO test VALUES(1,'2100-55020300',300)
INSERT INTO test VALUES(1,'2100-55020400',400)
INSERT INTO test VALUES(1,'2180-55020100',500)
INSERT INTO test VALUES(1,'2180-55020200',600)
INSERT INTO test VALUES(1,'2180-55020300',700)
INSERT INTO test VALUES(2,'2100-55020100',900)
INSERT INTO test VALUES(2,'2100-55020200',100)
INSERT INTO test VALUES(2,'2180-55020300',200)
INSERT INTO test VALUES(3,'2100-55020200',100)

现在需要将表结构变换成如下格式
no leibie 0100 0200 0300 0400
-----------------------------------------------------
1 2100-5502 100 200 300 400
1 2180-5502 500 600 700 
2 2100-5502 900 100  
2 2180-5502              200 
3 2100-5502       100  
问题描述：
对同一个no, 以及left(acctno,9)，根据acctno列的后四位重新排列数据，也就是将同一个no, 和left(acctno,9)的记录变成一行数据，但列随着right(acctno,4)不同而增加，并将相应的qty值放在对应的列下面。

--建交叉表的表结构
if exists (select * from sysobjects where objectproperty(object_id('shifttable'),'istable') = 1)
drop table shifttable
go
select distinct right(accno,4) shiftcolumn into shifttable from test

if exists (select * from sysobjects where objectproperty(object_id('crosstable'),'istable') = 1)
drop table crosstable
go
declare @sql nvarchar(4000),@column nvarchar(255)
select @sql='create table crosstable (no int not null,leibie nvarchar(255) COLLATE Latin1_General_BIN not null,'
declare cur_column cursor for select shiftcolumn from shifttable
open cur_column
fetch next from cur_column into @column
while @@fetch_status=0
begin
select @sql=@sql+'['+@column+']'+' nvarchar(30) default '''','
fetch next from cur_column into @column
end
select @sql=left(@sql,len(@sql)-1)+')'
exec(@sql)
close cur_column
deallocate cur_column

--往交叉表里insert准备关联的数据
insert crosstable(no,leibie)
select distinct no,left(accno,9) from test

--得到最后效果
if exists (select * from sysobjects where objectproperty(object_id('temptable'),'istable') = 1)
drop table temptable
go
declare @sql nvarchar(4000)
select cast(no as varchar(30)) no,left(accno,9) shiftcolumn1,right(accno,4) shiftcolumn2,cast(qty as nvarchar(30)) qty into temptable from test
declare @no varchar(30),@shiftcolumn1 nvarchar(255),@shiftcolumn2 nvarchar(30),@qty nvarchar(30)
declare cur_temp cursor for select no,shiftcolumn1,shiftcolumn2,qty from temptable
open cur_temp
fetch cur_temp into @no,@shiftcolumn1,@shiftcolumn2,@qty
while @@fetch_status=0
begin
select @sql='update crosstable set '+'['+@shiftcolumn2+']'+'='''+@qty+''''+' where
exec(@sql)
fetch cur_temp into @no,@shiftcolumn1,@shiftcolumn2,@qty
end
close cur_temp
deallocate cur_temp
select * from crosstable
drop table shifttable
drop table temptable
drop table crosstable

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/68303/viewspace-251513/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/68303/viewspace-251513/