SQL 2000行列式
SQL 2000行列式
Hi guys!
我有一个这样的表：
if exists (select 1
from sysobjects
where id = object_id( 'ProProcess ')
and type = 'U ')
drop table ProProcess
go
/*==============================================================*/
/* Table: ProProcess */
/*==============================================================*/
create table ProProcess (
PROP_ID bigint identity,
PROP_JOBNO varchar(10) not null,
PROP_PROCESS_NO varchar(30) not null,
PROP_CLASS_NO varchar(10) null,
PROP_MAC_NO varchar(30) null,
PROP_STARTDT datetime null,
PROP_FINISHDT datetime null,
PROP_CREATEDT datetime null,
PROP_UPDATEDT datetime null,
PROP_CREATER varchar(30) null,
constraint PK_PROPROCESS primary key (PROP_JOBNO, PROP_PROCESS_NO)
)
go
测试数据如下：
insert into ProProcess(PROP_JOBNO, PROP_PROCESS_NO, PROP_CLASS_NO, PROP_MAC_NO, PROP_STARTDT, PROP_FINISHDT)
select 'B0001 ', 'Get Order ', 'D ', NULL, '2007-05-02 12:00:00 ', '2007-05-02 12:01:00 ' union
select 'B0001 ', 'Make Film ', 'D ', NULL, '2007-05-02 12:05:00 ', '2007-05-02 12:11:00 ' union
select 'B0001 ', 'Open Paper ', 'D ', '01 ', '2007-05-02 12:12:00 ', '2007-05-02 12:15:00 ' union
select 'B0001 ', 'Printing ', 'D ', 'P02 ', '2007-05-02 12:17:00 ', '2007-05-02 12:37:00 ' union
select 'B0002 ', 'Get Order ', 'D ', NULL, '2007-05-02 12:00:00 ', '2007-05-02 12:01:00 ' union
select 'B0002 ', 'Make Film ', 'D ', NULL, '2007-05-02 12:05:00 ', '2007-05-02 12:11:00 ' union
select 'B0002 ', 'Open Paper ', 'D ', '02 ', '2007-05-02 12:12:00 ', '2007-05-02 12:15:00 ' union
select 'B0002 ', 'Printing ', 'D ', 'P01 ', '2007-05-02 12:17:00 ', '2007-05-02 12:37:00 '
GO
现在需要的结果为：
[PROP_JOBNO], [Get Order], [Get Order StartDT], [Get Order EndDT], [Make Film], [Make Film StartDT], [Make Film EndDT], [Open Paper], [Open Paper StartDT], [Open Paper EndDT], [Printing], [Printing StartDT], [Printing EndDT]
B0001, NULL, 2007-05-02 12:00:00,2007-05-02 12:01:00,NULL,2007-05-02 12:05:00,2007-05-02 12:11:00,01,2007-05-02 12:12:00,2007-05-02 12:15:00,P02,2007-05-02 12:17:00,2007-05-02 12:37:00
B0002, NULL, 2007-05-02 12:00:00,2007-05-02 12:01:00,NULL,2007-05-02 12:05:00,2007-05-02 12:11:00,02,2007-05-02 12:12:00,2007-05-02 12:15:00,P01,2007-05-02 12:17:00,2007-05-02 12:37:00
不知各位兄台有没有良方
[解决办法]
论坛内搜索 “行转列”
[解决办法]
select PROP_JOBNO,
max(CASE "PROP_PROCESS_NO " WHEN 'Get Order ' THEN "PROP_STARTDT " ELSE 0 END) AS 'Get Order StartDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Get Order ' THEN "PROP_FINISHDT " ELSE 0 END) AS 'Get Order EndDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Make Film ' THEN "PROP_STARTDT " ELSE 0 END) AS 'Make Film StartDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Make Film ' THEN "PROP_FINISHDT " ELSE 0 END) AS 'Make Film EndDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Open Paper ' THEN "PROP_STARTDT " ELSE 0 END) AS 'Open Paper StartDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Open Paper ' THEN "PROP_FINISHDT " ELSE 0 END) AS 'Open Paper EndDT ',
max(CASE "PROP_PROCESS_NO " WHEN 'Printing ' THEN "PROP_STARTDT " ELSE 0 END) AS 'Printing StartDT ' ,
max(CASE "PROP_PROCESS_NO " WHEN 'Printing ' THEN "PROP_FINISHDT " ELSE 0 END) AS 'Printing EndDT '
From ProProcess Group by "PROP_JOBNO "
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
set @sql= 'select PROP_JOBNO, '
select @sql=@sql+ 'PROP_PROCESS_NO=sum(case when PROP_PROCESS_NO= " '+PROP_PROCESS_NO+ ' " then PROP_PROCESS_NO end) ,(PROP_CLASS_NO+ "StartDT ")= PROP_STARTDT,(PROP_CLASS_NO+ "EndDT ")= PROP_FINISHDT , '
from (select distinct PROP_PROCESS_NO FROM ProProcess)
SELECT @sql=left(@sql,len(@sql) - 1)
select @sql=@sql+ ' from ProProcess group by PROP_JOBNO '
exec(@sql)
