if object_id('[表一]') is not null drop table [表一] go create table [表一]([单号] int,[店名] varchar(6)) insert [表一] select 1,'北京店' union all select 2,'上海店' union all select 3,'北京店' union all select 4,'上海店'goif object_id('[表二]') is not null drop table [表二] go create table [表二]([ID] int,[品种] varchar(4),[小计] int) insert [表二] select 1,'鸡肉',20 union all select 1,'牛肉',20 union all select 1,'鱼肉',20 union all select 2,'鸭肉',10 union all select 2,'猪肉',10;declare @sql varchar(8000); set @sql = 'select [品种]'; select @sql = @sql + ' ,sum(case when [店名] = ''' + [店名] + ''' then [小计] end) as [' + [店名] + ']' from (select distinct [店名] from [表一]) as x; select @sql = @sql + ' from (select * from [表一] A INNER JOIN [表二] B ON A.[单号] = B.[ID])x group by [品种]';exec(@sql);--测试结果: /* 品种 北京店 上海店 ---- ----------- ----------- 鸡肉 20 NULL 牛肉 20 NULL 鸭肉 NULL 10 鱼肉 20 NULL 猪肉 NULL 10 */
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([单号] int,[店名] varchar(6))
insert [表一]
select 1,'北京店' union all
select 2,'上海店' union all
select 3,'北京店' union all
select 4,'上海店'goif object_id('[表二]') is not null drop table [表二]
go
create table [表二]([ID] int,[品种] varchar(4),[小计] int)
insert [表二]
select 1,'鸡肉',20 union all
select 1,'牛肉',20 union all
select 1,'鱼肉',20 union all
select 2,'鸭肉',10 union all
select 2,'猪肉',10;declare @sql varchar(8000);
set @sql = 'select [品种]';
select @sql = @sql + ' ,sum(case when [店名] = ''' + [店名] + ''' then [小计] end) as ['
+ [店名] + ']'
from (select distinct [店名] from [表一]) as x;
select @sql = @sql + ' from (select * from [表一] A INNER JOIN [表二] B ON A.[单号] = B.[ID])x group by [品种]';exec(@sql);--测试结果:
/*
品种 北京店 上海店
---- ----------- -----------
鸡肉 20 NULL
牛肉 20 NULL
鸭肉 NULL 10
鱼肉 20 NULL
猪肉 NULL 10
*/