--> 测试数据:#a if object_id('tempdb.dbo.#a') is not null drop table #a create table #a(Period int) insert into #a select 201001 union all select 201002 union all select 201003 --> 测试数据:#b if object_id('tempdb.dbo.#b') is not null drop table #b create table #b(Customer varchar(8), Period int, sum int) insert into #b select 'test001', 201001, 300 union all select 'test002', 201001, 20 union all select 'test001', 201002, 40declare @cols varchar(8000) select @cols = isnull(@cols+',', '') + '['+ltrim(Period)+']=sum(case Period when '''+ltrim(Period)+''' then [sum] else 0 end)' from #a exec ('select Customer, '+@cols+' from #b group by Customer')/* Customer 201001 201002 201003 -------- ----------- ----------- ----------- test001 300 40 0 test002 20 0 0 */
--动态转换 declare @sql varchar(8000) set @sql = '' select @sql = @sql + ',max(case b.Period when ''' + ltrim(Period1) + ''' then ltrim([sum]) else '''' end) [' + ltrim(Period1)+ ']' from (select distinct Period Period1 from #a) as a --print @sql set @sql = 'select customer,' + substring(@sql,2,len(@sql)) + ' from #b b group by customer' --print @sql exec(@sql)customer 201001 201002 201003 -------- ------------ ------------ ------------ test001 300 40 test002 20 (2 行受影响)
create table A(Period varchar(10)) insert into a values('201001') insert into a values('201002') insert into a values('201003') create table b (Customer varchar(10),Period varchar(10),[sum] int) insert into b values('test001', '201001', 300) insert into b values('test002', '201001', 20) insert into b values('test001', '201002', 40) go--sql 2000静态,指A表period只有201001-201003这三个数据。 select b.Customer , max(case a.period when '201001' then ltrim(b.[sum]) else '--' end) [201001], max(case a.period when '201002' then ltrim(b.[sum]) else '--' end) [201002], max(case a.period when '201003' then ltrim(b.[sum]) else '--' end) [201003] from b , a where b.Period = a.Period group by b.customer /* Customer 201001 201002 201003 ---------- ------------ ------------ ------------ test001 300 40 -- test002 20 -- --(所影响的行数为 2 行) */--sql 2000动态,指A表period值不固定 declare @sql varchar(8000) set @sql = 'select b.customer ' select @sql = @sql + ' , max(case a.period when ''' + period + ''' then ltrim(b.[sum]) else ''--'' end) [' + period + ']' from (select distinct period from a) as t set @sql = @sql + ' from b , a where b.Period = a.Period group by b.customer' exec(@sql) /* Customer 201001 201002 201003 ---------- ------------ ------------ ------------ test001 300 40 -- test002 20 -- --(所影响的行数为 2 行) */ drop table a , b
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(Period int)
insert into #a
select 201001 union all
select 201002 union all
select 201003
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(Customer varchar(8), Period int, sum int)
insert into #b
select 'test001', 201001, 300 union all
select 'test002', 201001, 20 union all
select 'test001', 201002, 40declare @cols varchar(8000)
select @cols = isnull(@cols+',', '') + '['+ltrim(Period)+']=sum(case Period when '''+ltrim(Period)+''' then [sum] else 0 end)' from #a
exec ('select Customer, '+@cols+' from #b group by Customer')/*
Customer 201001 201002 201003
-------- ----------- ----------- -----------
test001 300 40 0
test002 20 0 0
*/
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case b.Period when ''' + ltrim(Period1) + ''' then ltrim([sum]) else '''' end) [' + ltrim(Period1)+ ']'
from (select distinct Period Period1 from #a) as a
--print @sql
set @sql = 'select customer,' + substring(@sql,2,len(@sql)) + ' from #b b group by customer'
--print @sql
exec(@sql)customer 201001 201002 201003
-------- ------------ ------------ ------------
test001 300 40
test002 20 (2 行受影响)
insert into a values('201001')
insert into a values('201002')
insert into a values('201003')
create table b (Customer varchar(10),Period varchar(10),[sum] int)
insert into b values('test001', '201001', 300)
insert into b values('test002', '201001', 20)
insert into b values('test001', '201002', 40)
go--sql 2000静态,指A表period只有201001-201003这三个数据。
select b.Customer ,
max(case a.period when '201001' then ltrim(b.[sum]) else '--' end) [201001],
max(case a.period when '201002' then ltrim(b.[sum]) else '--' end) [201002],
max(case a.period when '201003' then ltrim(b.[sum]) else '--' end) [201003]
from b , a
where b.Period = a.Period
group by b.customer
/*
Customer 201001 201002 201003
---------- ------------ ------------ ------------
test001 300 40 --
test002 20 -- --(所影响的行数为 2 行)
*/--sql 2000动态,指A表period值不固定
declare @sql varchar(8000)
set @sql = 'select b.customer '
select @sql = @sql + ' , max(case a.period when ''' + period + ''' then ltrim(b.[sum]) else ''--'' end) [' + period + ']'
from (select distinct period from a) as t
set @sql = @sql + ' from b , a where b.Period = a.Period group by b.customer'
exec(@sql)
/*
Customer 201001 201002 201003
---------- ------------ ------------ ------------
test001 300 40 --
test002 20 -- --(所影响的行数为 2 行)
*/
drop table a , b