create table table1 ( [编号] int, [日期] varchar(10), [数量] int ) insert table1 select 1,'2005-9-1',1 union select 2,'2005-9-2',2 union select 3,'2005-9-3',3 union select 2,'2005-9-4',4 union select 4,'2005-9-5',5 --查询 declare @bianhao int --要查询的编号 ,@sql varchar(8000) select @bianhao=2 ,@sql='' select @sql=@sql+',sum(case when [日期]='''+[日期]+''' then [数量] else 0 end) as '+quotename([日期]) from table1 where [编号]=@bianhao group by [日期] select @sql='select [编号]'+@sql+ ' from table1 where [编号]='+convert(varchar,@bianhao)+ ' group by [编号]' exec(@sql)--删除测试环境 drop table table1--结果 /* 编号 2005-9-2 2005-9-4 ----------- ----------- ----------- 2 2 4*/
from table1 where [编号]=@bianhao group by [日期] ===========> from table1 where [编号]=@bianhao group by [日期] order by [日期]
(
[编号] int,
[日期] varchar(10),
[数量] int
)
insert table1
select 1,'2005-9-1',1 union
select 2,'2005-9-2',2 union
select 3,'2005-9-3',3 union
select 2,'2005-9-4',4 union
select 4,'2005-9-5',5 --查询
declare @bianhao int --要查询的编号
,@sql varchar(8000)
select @bianhao=2
,@sql=''
select @sql=@sql+',sum(case when [日期]='''+[日期]+''' then [数量] else 0 end) as '+quotename([日期])
from table1 where [编号]=@bianhao group by [日期]
select @sql='select [编号]'+@sql+
' from table1 where [编号]='+convert(varchar,@bianhao)+
' group by [编号]'
exec(@sql)--删除测试环境
drop table table1--结果
/*
编号 2005-9-2 2005-9-4
----------- ----------- -----------
2 2 4*/
===========>
from table1 where [编号]=@bianhao group by [日期] order by [日期]