SQL2000动态表行转成列解决思路

SQL2000动态表行转成列
 
如下动态表：

  帐户( as int), 商品编号(as int),数量(as real)
  101 1001 5
  102 1001 3


需显示

 商品编号 101 102
  1001 5 3

[解决办法]

SQL code

use Tempdbgo--> -->  if not object_id(N'Tempdb..#T1') is null    drop table #T1GoCreate table #T1([帐户] int,[商品编号] int,[数量] int)Insert #T1select 101,1001,5 union allselect 102,1001,3Godeclare @s nvarchar(4000)set @s=''Select     @s=@s+N','+quotename(帐户)+N'=max(case when 帐户=N'+quotename(帐户,'''')+N' then 数量 else '''' end)'from #T1 group by 帐户--顯示生成語句print N'select 商品编号'+@s+N' from #T1 group by 商品编号'exec(N'select 商品编号'+@s+N' from #T1 group by 商品编号')go--SQL2005 declare @s nvarchar(4000)Select     @s=isnull(@s+',','')+quotename(帐户) from #T1 group by 帐户exec('select * from #T1 as a pivot (max(数量) for 帐户 in('+@s+'))b')/*商品编号    101    1021001    5    3*/
[解决办法]
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-12-30 12:55:07-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([帐户] int,[商品编号] int,[数量] int)insert [tb]select 101,1001,5 union allselect 102,1001,3--------------开始查询--------------------------declare @sql varchar(8000)set @sql = 'select 商品编号 'select @sql = @sql + ' , max(case 帐户 when ''' + ltrim(帐户) + ''' then 数量 else 0 end) [' + ltrim(帐户) + ']'from (select distinct 帐户 from tb) as aset @sql = @sql + ' from tb group by 商品编号'exec(@sql) ----------------结果----------------------------/* 商品编号        101         102----------- ----------- -----------1001        5           3(1 行受影响)*/