【IT168 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);
   
    select * from test；
    no accno qty
    ----------------------------------------------------------------------------------------
    1 2100-55020100 100.0
    1 2100-55020200 200.0
    1 2100-55020300 300.0
    1 2100-55020400 400.0
    1 2180-55020100 500.0
    1 2180-55020200 600.0
    1 2180-55020300 700.0
    2 2100-55020100 900.0
    2 2100-55020200 100.0
    2 2180-55020300 200.0
    3 2100-55020200 100.0
   
    现在需要将表结构变换成如下格式
    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值放在对应的列下面。
    

  
    
    --------------------------------------------------------------------------------
    作者：lodge    时间：04-06-05 22:16
   
    因有多年未从事SQL SERVER的开发, 语法已经忘光了, 下面的例子只是给你介绍一个思路,
    select no,
    left(acctno,9),
    sum(iif(right(acctno,4)="0100", qty, 0)),
    sum(iif(right(acctno,4)="0200", qty, 0)),
    sum(iif(right(acctno,4)="0300", qty, 0)),
    sum(iif(right(acctno,4)="0400", qty, 0))
    from test
    group by no,left(acctno,9)
   
   
    --------------------------------------------------------------------------------
    作者：magicangel    时间：04-06-06 19:35
   
    --建交叉表。 
    
    --建交叉表的表结构
    if exists (select * from sysobjects where objectproperty(object_id('shiftcolumn'),'istable') = 1)
    drop table shiftcolumn
    go
    select distinct right(accno,4) shiftcolumn into shiftcolumn 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 shiftcolumn
    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 no'+'='+@no+' and leibie='''+@shiftcolumn1+''''
    exec(@sql)
    fetch cur_temp into @no,@shiftcolumn1,@shiftcolumn2,@qty
    end
    close cur_temp
    deallocate cur_temp
    select * from crosstable
    drop table shiftcolumn
    drop table temptable
    drop table crosstable