22,209
社区成员
发帖
与我相关
我的任务
分享
create table #WorkPlan
(ID varchar(50) primary key ,[Year] varchar(15),
[Week] varchar(15),[Date] datetime,
[ModelName] varchar(15),[Products] varchar(50),
[Output] varchar(15),[Person] varchar(15),
[Demand] varchar(15),[Manpower] varchar(15),
[Plan] int,[Version] varchar(15),
[Path] varchar(15))
insert #WorkPlan
select '1','2018', '1', '2018-01-01', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 117, '1','' union all
select '2','2018', '1', '2018-01-02', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 117, '1','' union all
select '3','2018', '1', '2018-01-03', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 117, '1','' union all
select '4','2018', '1', '2018-01-04', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 118, '1','' union all
select '5','2018', '1', '2018-01-05', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 118, '1','' union all
select '6','2018', '1', '2018-01-06', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 118, '1','' union all
select '7','2018', '1', '2018-01-07', 'IBG', 'EX6-4983-IECF_SEMI', '26400', '1', '5000', '2', 1, '1','' union all
select '8','2018', '1', '2018-01-01', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 13, '1','' union all
select '9','2018', '1', '2018-01-02', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 14, '1','' union all
select '10','2018', '1', '2018-01-3', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 17, '1','' union all
select '11','2018', '1', '2018-01-4', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 14, '1','' union all
select '12','2018', '1', '2018-01-5', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 14, '1','' union all
select '13','2018', '1', '2018-01-6', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 12, '1','' union all
select '14','2018', '1', '2018-01-7', 'IBG', 'EX6-5183-IECF_SEMI', '26400', '1', '5000', '2', 0, '1',''
--测试数据结束
SELECT [YEAR],
[week],
ModelName,
Products,
[OUTPUT],
Person,
Demand,
Manpower,
[PLAN],
SUM([plan]) OVER(
PARTITION BY [YEAR],
[week],
ModelName,
Products,
[OUTPUT],
Person,
Demand,
Manpower
) AS sum_total,
DATENAME(weekday, [date]) AS dt
INTO #tb
FROM #WorkPlan
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[dt]+']' from #tb for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT * from #tb pivot(max([plan])for dt in('+@name+'))a'
PRINT @sql
EXEC( @sql)