如下动态表：
帐户( 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 @[email protected]+N','+quotename(帐户)+N'=max(case when 帐户=N'+quotename(帐户,'''')+N' then 数量 else '''' end)'from #T1 group by 帐户--顯示生成語句print N'select [email protected]+N' from #T1 group by 商品编号'exec(N'select [email protected]+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([email protected]+'))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 行受影响)*/