declare @sql varchar(8000)
set @sql='select 编号'
select @sql=@sql+',['+单位+']=isnull(sum(case when 单位='''+单位+''' then 数量 end),0)'
from tab
group by 单位
print @sql
select @sql=@sql+' from tab group by 编号'
print @sql
if exists (select * from sysobjects where object_id('tableA')=id and objectproperty(id,'isUserTable')=1)
drop table tableA
go
create table tableA(编号 int,单位 varchar(10),数量 int)
go
---------增加数据---------
insert tableA
select 1,'一队',100 union all
select 2,'二队',200 union all
select 3,'三队',300 union all
select 4,'四队',400 union all
select 5,'一队',500 union all
select 6,'一队',600 union all
select 7,'一队',700 union all
select 8,'二队',800 union all
select 9,'四队',900 union all
select 10,'五队',1000
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'case when 单位='''+单位+''' then 数量 end '''+单位+''',' from
(
select top 100 min(编号) bh,单位 from tableA group by 单位 order by 1
)a
set @sql='select 编号,'+left(@sql,len(@sql)-1)+' from tableA'
declare @s nvarchar(4000)
set @s = ''
select @s=@s+',['+dw+']= isnull(convert(varchar(10),sum(case dw when '''+dw+''' then qty end)),'''')' from ddd group by dw order by dw
set @s=stuff(@s,1,1,'select bh,')+' from ddd group by bh'
print @s
exec (@s)
go
create table ddd
(bh int,
dw nvarchar(5),
qty int )
insert ddd
select 1,'1队',100 union all
select 2,'2队',200 union all
select 3,'3队',300 union all
select 4,'4队',400 union all
select 5,'1队',500 union all
select 6,'1队',600 union all
select 7,'1队',700 union all
select 8,'2队',800 union all
select 9,'4队',900
go
declare @s nvarchar(4000)
set @s = ''
select @s=@s+',['+dw+']= sum(case dw when '''+dw+''' then qty else 0 end)' from ddd group by dw order by dw
set @s=stuff(@s,1,1,'select bh,')+' from ddd group by bh'
print @s
exec (@s)
go
--查看结果
declare @sql varchar(8000)
set @sql='select 编号'
select @sql=@sql+',['+单位+']=isnull(convert(varchar(20),sum(case when 单位='''+单位+''' then 数量 end),0),'''')'
from tab
group by 单位
print @sql
select @sql=@sql+' from tab group by 编号'
print @sql
--查看结果
declare @sql varchar(8000)
set @sql='select 编号'
select @sql=@sql+',['+单位+']=isnull(sum(case when 单位='''+单位+''' then 数量 end),0)'
from tab
group by 单位
print @sql
select @sql=@sql+' from tab group by 编号'
print @sql
select bh,
一队=(case dw when '一队' then qty else '' end),
二队=(case dw when '二队' then qty else '' end),
三队=(case dw when '三队' then qty else '' end),
四队=(case dw when '四队' then qty else '' end)
from ddd