27,579
社区成员
发帖
与我相关
我的任务
分享
这样?
declare @s nvarchar(1000)
set @s='select
b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,a.shuliang,a.[date]
from bupin as a
right join bupinlist as b on a.bupinlistid = b.bupinlistid and MONTH(a.date) = 9 where b.dalei = ''HTHT''
'
--上面是程序传参
--以下是存储过程调用
set @s=STUFF(@s,CHARINDEX('from',@s),0,' into #T ')
exec(
@s+'
declare @s nvarchar(4000)
set @s=''''
Select @s=@s+N'',''+quotename(CONVERT(varchar(10),Date,120))+N''=sum(case when CONVERT(varchar(10),Date,120)=N''+quotename(CONVERT(varchar(10),Date,120),'''''''')+N'' then shuliang end)''
from #T group by date
exec(N''select bupinlistid''+@s+N'',sum([shuliang]) as 合计 from #T group by bupinlistid'')
')
select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,a.shuliang,a.[date] from bupin as a right join bupinlist as b on a.bupinlistid = b.bupinlistid and MONTH(a.date) = 9 where b.dalei = 'HTHT'
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[bupinmingcheng] nvarchar(50) ,[bupinfanhao] nvarchar(50) ,[shuliang] int,[date] Datetime)
Insert #T
select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,a.shuliang,a.[date] from bupin as a right join bupinlist as b on a.bupinlistid = b.bupinlistid and MONTH(a.date) = 9 where b.dalei = 'HTHT'
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(CONVERT(varchar(10),Date,120))+N'=sum(case when CONVERT(varchar(10),Date,120)=N'+quotename(CONVERT(varchar(10),Date,120),'''')+N' then shuliang end)'
from #T group by date
--顯示生成語句
print N'select ID'+@s+N',sum([shuliang]) as 合计 from #T group by ID'
exec(N'select ID'+@s+N',sum([shuliang]) as 合计 from #T group by ID')
go
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[shuliang] int,[date] Datetime)
Insert #T
select 1,400,'2011-1-1' union all
select 1,500,'2011-1-1' union all
select 1,200,'2011-1-4' union all
select 2,100,'2011-1-5'
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(CONVERT(varchar(10),Date,120))+N'=sum(case when CONVERT(varchar(10),Date,120)=N'+quotename(CONVERT(varchar(10),Date,120),'''')+N' then shuliang end)'
from #T group by date
--顯示生成語句
print N'select ID'+@s+N',sum([shuliang]) as 合计 from #T group by ID'
exec(N'select ID'+@s+N',sum([shuliang]) as 合计 from #T group by ID')
go
/*
ID 2011-01-01 2011-01-04 2011-01-05 合计
1 900 200 NULL 1100
2 NULL NULL 100 100
*/
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[shuliang] int,[date] Datetime)
Insert #T
select 1,400,'2011-1-1' union all
select 1,500,'2011-1-1' union all
select 1,200,'2011-1-4' union all
select 2,100,'2011-1-5'
Go
--SQL2005
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename(CONVERT(varchar(10),Date,120)) from #T group by date
exec('select * from (select ID,CONVERT(varchar(10),Date,120) as date,shuliang from #T) as a pivot (max(shuliang) for date in('+@s+'))b')
/*
ID 2011-01-01 2011-01-04 2011-01-05
1 500 200 NULL
2 NULL NULL 100
*/
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[shuliang] int,[date] Datetime)
Insert #T
select 1,400,'2011-1-1' union all
select 1,500,'2011-1-1' union all
select 1,200,'2011-1-4' union all
select 2,100,'2011-1-5'
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(CONVERT(varchar(10),Date,120))+N'=max(case when CONVERT(varchar(10),Date,120)=N'+quotename(CONVERT(varchar(10),Date,120),'''')+N' then shuliang end)'
from #T group by date
--顯示生成語句
print N'select ID'+@s+N' from #T group by ID'
exec(N'select ID'+@s+N' from #T group by ID')
go
/*
ID 2011-01-01 2011-01-04 2011-01-05
1 500 200 NULL
2 NULL NULL 100
*/