--竖表变横表,交叉表查询算法
use pubs
GO
--建立测试表
create table TESTA(A varchar(20),B varchar(20),QTY int)
insert into TESTA
select 'A1','B1',2 union
select 'A2','B2',5 union
select 'A3','B1',4 union
select 'A1','B2',6
--显示竖表
select * from TESTA
--显示横表
declare @sql nvarchar(1000)
select @sql=''
select @sql=@sql+',['+B+']=max(case B when '''+B+''' then QTY else 0 end)'
from TESTA
group by B
print 'select A'+@sql+' from TESTA group by A'
exec('select A'+@sql+' from TESTA group by A')
--删除测试表
drop table TESTA
|