求助 SQL 动态生成列等相关问题

[black263]

有两张表
A表有一列
Period(列名)
201001
201002
201003

B表有三列
Customer Period sum(这一行为列名)

test001 201001 300
test002 201001 20
test001 201002 40

最后查询结果为:

Customer 201001 201002 201003(这一行为列名)
test001 300 40 --
test002 20 -- --


不会写相关SQL啊,请高手指导.非常感谢.

[SQLCenter]

--> 测试数据：#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, 40

declare @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
*/

[black263]

佩服,佩服啊,真是牛人.谢谢了.

[abuying]

--动态转换
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 行受影响)

[dawugui]

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