34,575
社区成员
发帖
与我相关
我的任务
分享
create table a
(
prod varchar(20),
dept varchar(20),
finishqty int,
startdate datetime ,
enddate datetime
)
insert into a values('pro1' ,'A' ,1000 ,'2011-06-01', '2011-06-02')
insert into a values('pro1' ,'B' ,800 ,'2011-06-04', '2011-06-05')
insert into a values('pro1' ,'C' ,600 ,'2011-06-06', '2011-06-07')
insert into a values('pro2' ,'A' ,1200 ,'2011-06-10', '2011-06-11')
insert into a values('pro2' ,'B' ,1000 ,'2011-06-13', '2011-06-15')
insert into a values('pro2' ,'C' ,500 ,'2011-06-15', '2011-06-18')
select prod,
sum(case when dept='A' then finishqty end) as deptAqty,
max(case when dept='A' then startdate end) as Astartdate,
max(case when dept='A' then enddate end) as Aenddate,
sum(case when dept='B' then finishqty end) as deptBqty,
max(case when dept='B' then startdate end) as Bstartdate,
max(case when dept='B' then enddate end) as Benddate,
sum(case when dept='C' then finishqty end) as deptCqty,
max(case when dept='C' then startdate end) as Cstartdate,
max(case when dept='C' then enddate end) as Cenddate
from a group by prod
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#worktable') is null
drop table #worktable
Go
Create table #worktable([prod] nvarchar(4),[dept] nvarchar(1),[finishqty] int,[startdate] Datetime,[enddate] Datetime)
Insert #worktable
select N'pro1',N'A',1000,'2011-06-01','2011-06-02' union all
select N'pro1',N'B',800,'2011-06-04','2011-06-05' union all
select N'pro1',N'C',600,'2011-06-06','2011-06-07' union all
select N'pro2',N'A',1200,'2011-06-10','2011-06-11' union all
select N'pro2',N'B',1000,'2011-06-13','2011-06-15' union all
select N'pro2',N'C',500,'2011-06-15','2011-06-18'
Go
declare @s nvarchar(4000)
set @s=''
Select
@s=@s+','+quotename('dept'+[dept]+N'Qty')+'=max(case when [dept]='''+[dept]+''' then [finishqty] end)'
+','+quotename([dept]+N'startdate')+'=max(case when [dept]='''+[dept]+''' then convert(varchar(10),[startdate],120) end)'
+','+quotename([dept]+N'startdate')+'=max(case when [dept]='''+[dept]+''' then convert(varchar(10),[enddate],120) end)'
from #worktable
group by [dept]
exec('select [prod]'+@s+' from #worktable group by [prod] ')
/*
prod deptAQty Astartdate Astartdate deptBQty Bstartdate Bstartdate deptCQty Cstartdate Cstartdate
pro1 1000 2011-06-01 2011-06-02 800 2011-06-04 2011-06-05 600 2011-06-06 2011-06-07
pro2 1200 2011-06-10 2011-06-11 1000 2011-06-13 2011-06-15 500 2011-06-15 2011-06-18
*/
create table worktable(prod nvarchar(10),dept nvarchar(10),finishqty int,startdate datetime,enddate datetime)
insert into worktable select 'pro1','A',1000,'2011-06-01','2011-06-02'
insert into worktable select 'pro1','B',800,'2011-06-04','2011-06-05'
insert into worktable select 'pro1','C',600,'2011-06-06','2011-06-07'
insert into worktable select 'pro2','A',1200,'2011-06-10','2011-06-11'
insert into worktable select 'pro2','B',1000,'2011-06-13','2011-06-15'
insert into worktable select 'pro2','C',500,'2011-06-15','2011-06-18'
go
select prod,
sum(case when dept='A' then finishqty end) as deptAqty,
max(case when dept='A' then startdate end) as Astartdate,
max(case when dept='A' then enddate end) as Aenddate,
sum(case when dept='B' then finishqty end) as deptBqty,
max(case when dept='B' then startdate end) as Bstartdate,
max(case when dept='B' then enddate end) as Benddate,
sum(case when dept='C' then finishqty end) as deptCqty,
max(case when dept='C' then startdate end) as Cstartdate,
max(case when dept='C' then enddate end) as Cenddate
from worktable group by prod
/*
prod deptAqty Astartdate Aenddate deptBqty Bstartdate Benddate deptCqty Cstartdate Cenddate
---------- ----------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- ----------- ----------------------- -----------------------
pro1 1000 2011-06-01 00:00:00.000 2011-06-02 00:00:00.000 800 2011-06-04 00:00:00.000 2011-06-05 00:00:00.000 600 2011-06-06 00:00:00.000 2011-06-07 00:00:00.000
pro2 1200 2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 1000 2011-06-13 00:00:00.000 2011-06-15 00:00:00.000 500 2011-06-15 00:00:00.000 2011-06-18 00:00:00.000
(2 行受影响)
*/
go
drop table worktable