[code delphi]
procedure TFPRD._GetProdDay;
var
xSQL:String;
xCount:Integer;
begin
xCount:=0;
xProdD:='';
xSQLFName:='';
try
DM1.ADOCommand_UpdateSQL.CommandText:=' if (select OBJECT_ID(''tempdb..#Temp_ProdDay'')) IS NOT NULL Drop Table #Temp_ProdDay '
' select convert(varchar(11),dateadd(dd,number,' QuotedStr(Query_Search.FieldByName('ProdDayS').AsString) '),112) as ProdDay '
' into #Temp_ProdDay '
' from master..spt_values '
' where type=''p'' and '
' number < datediff(dd,' QuotedStr(Query_Search.FieldByName('ProdDayS').AsString) ',' QuotedStr(Query_Search.FieldByName('ProdDayE').AsString) ') 1';
DM1.ADOCommand_UpdateSQL.Execute;
xSQL:=' select ProdDay,''D_'' Convert(varchar(11),(ProdDay),112) as TrProdDay from #Temp_ProdDay ';
DM1._Query_table(DM1.Qtmp,xSQL);
if not DM1.Qtmp.IsEmpty then begin

DM1.Qtmp.First;
While not DM1.Qtmp.Eof do begin
xCount:=xCount 1;
Case xCount of
1:begin
xProdD :=('[' DM1.Qtmp.FieldByName('TrProdDay').AsString ']');
xSQLFName:='isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') as ''' DM1.Qtmp.FieldByName('ProdDay').AsString '''';
xSubFName:='isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') ';
end else begin
xProdD :=xProdD ',' ('[' DM1.Qtmp.FieldByName('TrProdDay').AsString ']');
xSQLFName:=xSQLFName ',' 'isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') as ''' DM1.Qtmp.FieldByName('ProdDay').AsString '''';
xSubFName:=xSubFName ' ' 'isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') ';
end;
end;
DM1.Qtmp.Next;
end;
end else begin
FunU._ShowMes('捉取不到生產天數');
Abort;
end;
except
FunU._ShowMes('匯整生產天數失敗，請再重新查詢一次。');
Abort;
end;
end;

procedure TFPRD.Query_SearchAfterScroll(DataSet: TDataSet);
var
xSQL:String;
i:integer;
xProdNo:String;
begin
inherited;

if DBGrid_D.Hint = 'UnCheck' then
Exit;
if Query_Search.FieldByName('IsDelete').AsString='1' then
Exit;
//**先串起所需天數的語法
_GetProdDay;

//**刪除已建立欄位
while DBGrid_D.Columns.Count > 8 do
DBGrid_D.Columns.Delete(DBGrid_D.Columns.Count-1);
try
FPRD.Enabled := False;
Query_D.DisableControls;
try
DM1.ADOConnection_DB.BeginTrans;
xSQL:='--先crate一個早、晚班、小計的暫存表 '
' if (select OBJECT_ID(''tempdb..#Temp_DS'')) IS NOT NULL Drop Table #Temp_DS '
' CREATE TABLE #Temp_DS( [ShiftT] [nVarchar](6) NOT NULL, '
' [ShiftTNa] [nVarchar](6) NOT NULL ) '
' insert into #Temp_DS values(''D'',' QuotedStr(A2UToBIG5(ChangeStr('早班'))) ') '
' insert into #Temp_DS values(''S'',' QuotedStr(A2UToBIG5(ChangeStr('晚班'))) ') '
' insert into #Temp_DS values(''SumDay'',' QuotedStr(A2UToBIG5(ChangeStr('小計'))) ') '

' --先匯整早、晚班PRDPDRM、PRDPDPD資料利用union all的方式把小計納進來 '
' if (select OBJECT_ID(''tempdb..#PRD_SumPQty'')) IS NOT NULL Drop Table #PRD_SumPQty '
' select M.CorpNo,M.ShiftT,D.MPSPCTNo,D.ProdNo, '
' ''D_'' Convert(varchar(11),(M.ProduceD),112) as ProduceD,SUM(D.PQty-D.TotBQty) as PQty '
' into #PRD_SumPQty '
' from PRDPDRM M,PRDPDRD D '
' where M.CorpNo=D.CorpNo '
' and M.CHNo=D.CHNo '
' and M.IsDelete<>''1'' '
' and M.CorpNo=' QuotedStr(Query_Search.fieldByName('CorpNo').AsString)
' and M.PType=' QuotedStr(LeftStr(ComboBox_PType.Text,1))
' and isnull(D.MPSPCTNo,'''')=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString)
' group by M.CorpNo,M.ShiftT,D.MPSPCTNo,D.ProdNo,M.ProduceD '
' union all '
' select M.CorpNo,''SumDay'' as ShiftT,D.MPSPCTNo,D.ProdNo, '
' ''D_'' Convert(varchar(11),(M.ProduceD),112) as ProduceD,SUM(D.PQty-D.TotBQty) as PQty '
' from PRDPDRM M,PRDPDRD D '
' where M.CorpNo=D.CorpNo '
' and M.CHNo=D.CHNo '
' and M.IsDelete<>''1'' '
' and M.CorpNo=' QuotedStr(Query_Search.fieldByName('CorpNo').AsString)
' and M.PType=' QuotedStr(LeftStr(ComboBox_PType.Text,1))
' and isnull(D.MPSPCTNo,'''')=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString)
' group by M.CorpNo,D.MPSPCTNo,D.ProdNo,M.ProduceD '

' --先把班別表和生產數做結合 '
' if (select OBJECT_ID(''tempdb..#PRD_ShiftT'')) IS NOT NULL Drop Table #PRD_ShiftT '
' select DS.*,M.CorpNo,M.MPSPCTNo,M.ProdNo,M.ProduceD '
' into #PRD_ShiftT '
' from #Temp_DS DS,(select CorpNo,MPSPCTNo,ProdNo,ProduceD from #PRD_SumPQty '
' group by CorpNo,MPSPCTNo,ProdNo,ProduceD) M '
' order by M.ProduceD,M.ProdNo,DS.ShiftT '

' --結合 '
' if (select OBJECT_ID(''tempdb..#PRD_TD'')) IS NOT NULL Drop Table #PRD_TD '
' select ST.*,isnull(DSum.PQty,''0'') as DSumQty '
' into #PRD_TD '
' from #PRD_ShiftT ST '
' left join #PRD_SumPQty DSum on ST.ShiftT COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ShiftT COLLATE Chinese_PRC_Stroke_CI_AS and '
' ST.CorpNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.CorpNo COLLATE Chinese_PRC_Stroke_CI_AS and '
' ST.MPSPCTNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.MPSPCTNo COLLATE Chinese_PRC_Stroke_CI_AS and '
' ST.ProdNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ProdNo COLLATE Chinese_PRC_Stroke_CI_AS and '
' ST.ProduceD COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ProduceD COLLATE Chinese_PRC_Stroke_CI_AS '

' --將匯整出來的資料轉成橫向 '
' if (select OBJECT_ID(''tempdb..#PRD_PIVOTQty'')) IS NOT NULL Drop Table #PRD_PIVOTQty '
' select * into #PRD_PIVOTQty '
' from (select * from #PRD_TD) as xx '
' PIVOT '
' (sum(DSumQty) '
' for ProduceD '
' in( ' xProdD ' )) as xxx ';
//FunU._WriteWideString2File(FunU._GetDesktopDir 'FPRD_1.txt',xSQL);
DM1.ADOCommand_UpdateSQL.CommandText :=xSQL;
DM1.ADOCommand_UpdateSQL.Execute;
DM1.ADOConnection_DB.CommitTrans;
except
DM1.ADOConnection_DB.RollbackTrans;
FunU._ShowMes('資料匯總錯誤，請再重試一次');
Abort;
end; //**try except
Finally
//**--將生產數最終結果與生產管制表結合
DM1.SQL_Command:=' if (select OBJECT_ID(''tempdb..#PRD_Finaly'')) IS NOT NULL Drop Table #PRD_Finaly '
' select M.PCTNo,M.ProdNo,P.PnameD '
' ,Convert(varchar(10),M.DNeedQty) as DNeedQty,Convert(varchar(10),isnull(M.BondedQty,0)) as BondedQty '
' ,ISNULL(M.CusNa,'''') as CusNa,ISNULL(M.Rem,'''') as Rem,isnull(M.ShipRem,'''') as ShipRem,isnull(D.ShiftTNa,'''') as ShiftT,' xSQLFName
' ,( ' xSubFName ' ) as SumPQty '
' into #PRD_Finaly '
' from MPSPCTD M '
' left join #PRD_PIVOTQty D on M.CorpNo=D.CorpNo and M.PCTNo=D.MPSPCTNo and M.ProdNo=D.ProdNo '
' ,Prod P '
' where M.ProdNo=P.ProdNo '
' and M.PCTNo=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString)
' order by M.ProdNo';
//FunU._WriteWideString2File(FunU._GetDesktopDir 'FPRD_2.txt',DM1.SQL_Command);
DM1.ADOCommand_UpdateSQL.CommandText:=DM1.SQL_Command;
DM1.ADOCommand_UpdateSQL.Execute;
DM1.SQL_Command:='select * from #PRD_Finaly order by ProdNo ';
DM1._Query_table(Query_D,DM1.SQL_Command);
//**設定DBGrid_D顯示格式
for i:=0 to Query_D.FieldCount-1 do begin
if ( (Query_D.Fields[i].FieldName = 'PCTNo') or
(Query_D.Fields[i].FieldName = 'ProdNo') or
(Query_D.Fields[i].FieldName = 'PnameD') or
(Query_D.Fields[i].FieldName = 'DNeedQty') or
(Query_D.Fields[i].FieldName = 'BondedQty') or
(Query_D.Fields[i].FieldName = 'CusNa') or
(Query_D.Fields[i].FieldName = 'Rem') or
(Query_D.Fields[i].FieldName = 'ShipRem') or
(Query_D.Fields[i].FieldName = 'ShiftT') ) then
Continue;

DBGrid_D.Columns.Add;
DBGrid_D.Columns[DBGrid_D.Columns.Count-1].FieldName := Query_D.Fields[i].FieldName;
if Query_D.Fields[i].FieldName = 'SumPQty' then
DBGrid_D.Columns[DBGrid_D.Columns.Count-1].Title.Caption := ChangeStr('合計')
else
DBGrid_D.Columns[DBGrid_D.Columns.Count-1].Title.Caption := Query_D.Fields[i].FieldName;
end;

//**設定DBGrid_D 欄位顯示否
xProdNo:='';
Query_D.First;
While not Query_D.Eof do begin
if xProdNo=Query_D.FieldByName('ProdNo').AsString then begin
Query_D.Edit;
Query_D.FieldByName('ProdNo').AsString:=' ';
Query_D.FieldByName('PnameD').AsString:=' ';
Query_D.FieldByName('DNeedQty').AsString:=' ';
Query_D.FieldByName('BondedQty').AsString:=' ';
Query_D.FieldByName('CusNa').AsString:=' ';
Query_D.FieldByName('Rem').AsString:=' ';
Query_D.FieldByName('ShipRem').AsString:=' ';
end else
xProdNo:=Query_D.FieldByName('ProdNo').AsString;
Query_D.Next;
end;
Query_D.First;
Query_D.EnableControls;
FPRD.Enabled := True;
end; //**try Finally

end;
[/code]