老问题：SQL 行转列问题

[号天大教主]

表内容如下：
company_id bank_id amount
1001 中行 100.00
1002 建行 200.00
1002 建行 300.00
1001 招行 400.00

怎么用一句SQL转化成以下形式呀？

company_id 中行 <-bank_id,不固定 建行 招行 。。。。
1001 100.00 0.00 400.00
1002 0.00 500.00 0.00

银行是不固定的，可能还有其它银行，具体有多少未知，请帮我用一句SQL实现下。

[dawugui]

declare @sql varchar(8000)
set @sql = 'select company_id '
select @sql = @sql + ' , sum(case bank_id when ''' + bank_id + ''' then amount else 0 end) [' + bank_id + ']'
from (select distinct bank_id from tb) as a
set @sql = @sql + ' from tb group by company_id'
exec(@sql) 

[sdhdy]

declare @sql varchar(8000)
set @sql = 'select company_id '
select @sql = @sql + ' , max(case bank_id when ''' + ltrim(bank_id) + ''' then amount else 0 end) [' + ltrim(bank_id) + ']'
from (select distinct bank_id from tb) as a
set @sql = @sql + ' from tb group by company_id '
exec(@sql) 

[子陌红尘]

declare @sql varchar(8000)
set @sql='select company_id'

select @sql=@sql+',['+bank_id+']=sum(case bank_id when '''+bank_id+''' then amount esle 0 end)'
from (select distinct bank_id from 表) t

set @sql=@sql+' from 表 group by company_id'

exec(@sql)

[dawugui]

create table tb(company_id int,   bank_id varchar(10),   amount decimal(18,2))
insert into tb values(1001 ,           '中行' ,     100.00 )
insert into tb values(1002 ,           '建行' ,     200.00 )
insert into tb values(1002 ,           '建行' ,     300.00 )
insert into tb values(1001 ,           '招行' ,     400.00 )
go

declare @sql varchar(8000)
set @sql = 'select company_id '
select @sql = @sql + ' , sum(case bank_id when ''' + bank_id + ''' then amount else 0 end) [' + bank_id + ']'
from (select distinct bank_id from tb) as a
set @sql = @sql + ' from tb group by company_id'
exec(@sql) 

drop table tb

/*
company_id  建行                                       招行                                       中行                                       
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
1001        .00                                      400.00                                   100.00
1002        500.00                                   .00                                      .00

*/

[sdhdy]

 
create table tb(company_id varchar(10),    bank_id varchar(10),    amount decimal(18,2))
go
insert tb select 1001  ,          '中行' ,     100.00 
insert tb select 1002  ,          '建行' ,     200.00 
insert tb select 1002  ,         '建行' ,     300.00 
insert tb select 1001   ,         '招行',      400.00 
go
declare @sql varchar(8000)
set @sql = 'select company_id '
select @sql = @sql + ' , max(case bank_id when ''' + ltrim(bank_id) + ''' then amount else 0 end) [' + ltrim(bank_id) + ']'
from (select distinct bank_id from tb) as a
set @sql = @sql + ' from tb group by company_id '
exec(@sql) 

go
drop table tb
go
/*
company_id 建行                   招行                   中行                   
---------- -------------------- -------------------- -------------------- 
1001       .00                  400.00               100.00
1002       300.00               .00                  .00
*/

[sdhdy]

create table tb(company_id int,    bank_id varchar(10),    amount decimal(18,2))
go
insert tb select 1001  ,          '中行' ,     100.00 
insert tb select 1002  ,          '建行' ,     200.00 
insert tb select 1002  ,         '建行' ,     300.00 
insert tb select 1001   ,         '招行',      400.00 
go
declare @sql varchar(8000)
set @sql = 'select company_id '
select @sql = @sql + ' , sum(case bank_id when ''' + ltrim(bank_id) + ''' then amount else 0 end) [' + ltrim(bank_id) + ']'
from (select distinct bank_id from tb) as a
set @sql = @sql + ' from tb group by company_id '
print @sql
exec(@sql) 

go
drop table tb
go
/*
company_id  建行                                       招行                                       中行                                       
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
1001        .00                                      400.00                                   100.00
1002        500.00                                   .00                                      .00
*/

[htl258_Tony]

现在回这种贴子速度越来越快了.

[号天大教主]

各位高手能不能把print(@sql)显示出来的sql贴出来呀？

[htl258_Tony]

select company_id  ,
     sum(case bank_id when '建行' then amount else 0 end) [建行] ,
     sum(case bank_id when '招行' then amount else 0 end) [招行] ,
     sum(case bank_id when '中行' then amount else 0 end) [中行]
from tb group by company_id 

[ai_li7758521]

if object_id('tb') is not null drop table tb
create table tb(company_id nvarchar(10),bank_id nvarchar(20),amount money)

insert tb
select '1001','中行',100.00 union all
select '1002','建行',200.00 union all
select '1002','建行',300.00 union all
select '1001','招行',400.00 

--动态SQL(2005)。
declare @CMD varchar(8000),@CMD1 varchar(8000)
select @CMD = isnull(@CMD + '],[' , '') +  bank_id from tb group by  bank_id
set @CMD ='[' + @CMD + ']'
exec ('select * from (select company_id, bank_id,amount from tb) a PIVOT(sum(amount) for bank_id in ('+@CMD+')) b')
/*
company_id 建行                    招行                    中行
---------- --------------------- --------------------- ---------------------
1001       NULL                  400.00                100.00
1002       500.00                NULL                  NULL

(2 行受影响)

*/