,ISNULL(MAX(CASE WHEN MON_ID = '10' THEN PROJ_PRICE END),0) AS MON10PROJ_PRICE
,ISNULL(MAX(CASE WHEN MON_ID = '11' THEN PROJ_COUNT END),0) AS MON11PROJ_COUNT
,ISNULL(MAX(CASE WHEN MON_ID = '11' THEN PROJ_PRICE END),0) AS MON11PROJ_PRICE
,ISNULL(MAX(CASE WHEN MON_ID = '12' THEN PROJ_COUNT END),0) AS MON12PROJ_COUNT
,ISNULL(MAX(CASE WHEN MON_ID = '12' THEN PROJ_PRICE END),0) AS MON12PROJ_PRICE FROM AAA GROUP BY YEA_ID
------------------------------------------------------------------------------------------------------------------------
將一個表的數據分成兩列 例子:
str_sql := ' SELECT m.position_name1,m.people_name1,m.tel_no1' +' ,n.position_name2,n.people_name2,n.tel_no2' +' FROM ( SELECT a.position_name AS
position_name1,a.people_name AS people_name1,a.tel_no AS tel_no1'
+' ,row_number() OVER(ORDER BY GETDATE()) row'
+' FROM sps_organization_position a'
+' LEFT JOIN pms_project b on b.proj_id = a.proj_id' +' WHERE a.proj_id= '''+proj_id+'''' +' ) m'
+' LEFT JOIN ( select x.position_name as
position_name2,x.people_name AS people_name2,x.tel_no AS tel_no2'
+' ,row_number() OVER(ORDER BY GETDATE()) row'
+' FROM sps_organization_position x' +' WHERE proj_id= '''+proj_id+''''
+' ) n ON m.row = n.row-1 AND n.row %2 = 0' +' WHERE m.row % 2 = 1';
------------------------------------------------------------------------------------------------------------------------
Sql中游標的使用: 例子:
/*
組 別: 工貿組--CHSYS(常宏)
系統名稱: DC文件\\現場項目管理系統 系統編號: D21
文件編號: pkSPS_WorkProgressIn 文件名稱: 工程進度計劃
功 能: 自動填充工程施工日期明細 編 寫: LHM
日 期: 2011-09-10 */
USE CHDPSDB Go
IF exists (select * from dbo.sysobjects where id =
object_id(N'dbo.Sps_Proc_work_daily') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop procedure dbo.Sps_Proc_work_daily Go
Create Procedure dbo.Sps_Proc_work_daily
@proj_id varchar(20) AS BEGIN
Declare @s_date smalldatetime Declare @e_date smalldatetime Declare @is_stop char(1) Declare @has_qty char(1)
Declare @w_date smalldatetime
Select @s_date=work_sdate From sps_proj_target Where proj_id = @proj_id Select @e_date=work_edate From sps_proj_target Where proj_id = @proj_id Select @w_date=@s_date Select @is_stop='N'
IF object_id('tempdb.dbo.#SPS_WorkProgressIn_daily') is not null drop table dbo.#SPS_WorkProgressIn_daily
Create table dbo.#SPS_WorkProgressIn_daily(day_seq smallint primary key identity(1,1),proj_id varchar(20),work_date smalldatetime ,
has_qty varchar(1),is_stop varchar(1)) --遍歷施工日期范圍，自動生成日期明細 While @w_date<=@e_date Begin
--獲取'停工'日期的所在范圍
Declare stopdate_cursor CURSOR FOR Select a.defer_sdate,b.defer_sdate From pms_defer_report a left join pms_defer_report b On a.proj_id=b.proj_id and a.defer_no=b.defer_no-1 and b.workingbz=3 and b.confirm_state=3
Where a.workingbz=1 and a.confirm_state=3 and a.proj_id=@proj_id Declare @s_Tmpdate smalldatetime Declare @e_Tmpdate smalldatetime
Open stopdate_cursor
Fetch next From stopdate_cursor InTo @s_Tmpdate,@e_Tmpdate While(@@Fetch_Status = 0) Begin
--判斷當前日期是否處在'停工'范圍，如是則添加'停工'標簽
IF @w_date >= @s_Tmpdate and @w_date Set @is_stop='N' Fetch next From stopdate_cursor InTo @s_Tmpdate,@e_Tmpdate End Close stopdate_cursor Deallocate stopdate_cursor --判斷當前日期是否存在子分項錄入工程量，如是則添加'已入工程量'標簽 Select @has_qty='N' Select @has_qty=Case isnull(sum(adjust_qty),0) when 0 then 'N' else 'Y' end From sps_work_progress_day_qty where proj_id=@proj_id and work_date=@w_date Group by proj_id,work_date Insert into dbo.#SPS_WorkProgressIn_daily(proj_id,work_date,has_qty,is_stop) Values(@proj_id,@w_date,@has_qty,@is_stop) Set @w_date=dateadd(day,1,@w_date) End SELECT * FROM dbo.#SPS_WorkProgressIn_daily END Go --EXEC dbo.Sps_Proc_work_daily '2007-DD-0220' --Go ------------------------------------------------------------------------------------------------------------------------ Dbgrid中數據隔行用不同顏色 例子: procedure TFrm_BMS_JSW_DtlRp.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState); begin if gdSelected in State then Exit; if (Sender as TDBGrid).DataSource.DataSet.RecNo mod 2=0 then (Sender as TDBGrid).Canvas.Brush.Color:=clMoneyGreen; (Sender as TDBGrid).DefaultDrawColumnCell(Rect,DataCol,Column,State); end; ------------------------------------------------------------------------------------------------------------------------
