27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #1 (
ID INT,
Seat INT,
dates VARCHAR(30),
Qty INT,
SumQty INT
)
INSERT INTO #1
SELECT 1 , 201501, '24日 18:00-24:00', 200 , 200 union all
select 1,201501 , '25日 24:00-08:00', 265, 465 union all
SELECT 1, 201501, '25日 08:00-18:00', 300 , 765
DECLARE @a varchar(max)
SELECT @a='select ID,Seat '
SELECT @a=@a+
','+QUOTENAME('日期')+N'=max(CASE dates WHEN '+QUOTENAME(dates,N'''')+N' THEN dates END)'+
','+QUOTENAME('完成数量')+N'=sum(CASE dates WHEN '+QUOTENAME(dates,N'''')+N' THEN Qty END)'+
','+QUOTENAME('累计数量')+N'=sum(CASE dates WHEN '+QUOTENAME(dates,N'''')+N' THEN SumQty END)'
FROM #1
GROUP BY dates
exec(@a+N'
FROM #1
GROUP BY ID,Seat')