表TB_Node
NodeID NodeOrder NodeName Re
1 1 开工
2 2 铺底
3 3 下水
4 4 交工
......不确定有多少表TP_ProjectFeatureNodePlan
ProjectID NodeID StartNodeID EndNodeID RelativelyTime
1 1 1 2 0.333
1 2 2 3 0.333
1 3 3 4 0.333
1 4 4 .. 0.333
2 1 1 1 0.5
2 2 2 2 0.5
2 3 3 3 0.5
2 4 4 4 0.5
...........不确定有多少
StartNodeID EndNodeID RelativelyTime是根据两个节点的时间和相对时间计算出该节点的具体时间,跟这个查询没关。表TP_RollingPlanNodePlan
PlanVersion ProjectID NodeID PlanTime
20060101 1 1 2006-11-01
20060101 1 3 2006-11-10
20060101 1 4 2006-11-20
20060101 2 1 2006-11-20
20060101 2 2 2006-11-30我想要的结果是:
ProjectID 开工 铺底 下水 交工
1 2006-11-01 null 2006-11-10 2006-11-20
2 2006-11-20 2006-11-30 null null
NodeID NodeOrder NodeName Re
1 1 开工
2 2 铺底
3 3 下水
4 4 交工
......不确定有多少表TP_ProjectFeatureNodePlan
ProjectID NodeID StartNodeID EndNodeID RelativelyTime
1 1 1 2 0.333
1 2 2 3 0.333
1 3 3 4 0.333
1 4 4 .. 0.333
2 1 1 1 0.5
2 2 2 2 0.5
2 3 3 3 0.5
2 4 4 4 0.5
...........不确定有多少
StartNodeID EndNodeID RelativelyTime是根据两个节点的时间和相对时间计算出该节点的具体时间,跟这个查询没关。表TP_RollingPlanNodePlan
PlanVersion ProjectID NodeID PlanTime
20060101 1 1 2006-11-01
20060101 1 3 2006-11-10
20060101 1 4 2006-11-20
20060101 2 1 2006-11-20
20060101 2 2 2006-11-30我想要的结果是:
ProjectID 开工 铺底 下水 交工
1 2006-11-01 null 2006-11-10 2006-11-20
2 2006-11-20 2006-11-30 null null
drop table TB_Node
go
create table TB_Node
(
NodeID int,
NodeOrder int,
NodeName varchar(10),
Re varchar(10)
)
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(1, 1, '开工','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(2, 2, '铺底','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(3, 3, '下水','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(4, 4, '交工','')
if object_id('pubs..TP_RollingPlanNodePlan') is not null
drop table TP_RollingPlanNodePlan
go
create table TP_RollingPlanNodePlan
(
PlanVersion varchar(10),
ProjectID int,
NodeID int,
PlanTime datetime
)
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 1, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 3, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 4, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 1, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 2, '2006-11-01')if object_id('pubs..test') is not null
drop table test
go--将结果放入一临时表
select a.ProjectID , a.NodeID, convert(varchar(10),a.PlanTime,120) as PlanTime , b.NodeName
into test
from TP_RollingPlanNodePlan a , TB_Node b
where a.NodeID = b.NodeID--用动态SQL实现结果
declare @sql varchar(8000)
set @sql = 'select ProjectID'
select @sql = @sql + ' , max(case NodeName when ''' + NodeName + ''' then PlanTime else null end) [' + NodeName + ']'
from (select distinct NodeName from test) as a
set @sql = @sql + ' from test group by ProjectID'
exec(@sql)
drop table TB_Node
drop table TP_RollingPlanNodePlan
drop table test
ProjectID 交工 开工 铺底 下水
----------- ---------- ---------- ---------- ----------
1 2006-11-01 2006-11-01 NULL 2006-11-01
2 NULL 2006-11-01 2006-11-01 NULL
drop table TB_Node
go
create table TB_Node
(
NodeID int,
NodeOrder int,
NodeName varchar(10),
Re varchar(10)
)
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(1, 1, '开工','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(2, 2, '铺底','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(3, 3, '下水','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(4, 4, '交工','')
if object_id('pubs..TP_RollingPlanNodePlan') is not null
drop table TP_RollingPlanNodePlan
go
create table TP_RollingPlanNodePlan
(
PlanVersion varchar(10),
ProjectID int,
NodeID int,
PlanTime datetime
)
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 1, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 3, '2006-11-10')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 4, '2006-11-20')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 1, '2006-11-20')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 2, '2006-11-30')if object_id('pubs..test') is not null
drop table test
go--将结果放入一临时表
select a.ProjectID , a.NodeID, convert(varchar(10),a.PlanTime,120) as PlanTime , b.NodeName
into test
from TP_RollingPlanNodePlan a , TB_Node b
where a.NodeID = b.NodeID--用动态SQL实现结果
declare @sql varchar(8000)
set @sql = 'select ProjectID'
select @sql = @sql + ' , max(case NodeName when ''' + NodeName + ''' then PlanTime else null end) [' + NodeName + ']'
from (select distinct NodeName from test) as a
set @sql = @sql + ' from test group by ProjectID'
exec(@sql)
drop table TB_Node
drop table TP_RollingPlanNodePlan
drop table test
ProjectID 交工 开工 铺底 下水
----------- ---------- ---------- ---------- ----------
1 2006-11-20 2006-11-01 NULL 2006-11-10
2 NULL 2006-11-20 2006-11-30 NULL
drop table TB_Node
go
create table TB_Node
(
NodeID int,
NodeOrder int,
NodeName varchar(10),
Re varchar(10)
)
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(1, 1, '1、开工','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(2, 2, '2、铺底','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(3, 3, '3、下水','')
insert into TB_Node(NodeID, NodeOrder, NodeName, Re) values(4, 4, '4、交工','')
if object_id('pubs..TP_RollingPlanNodePlan') is not null
drop table TP_RollingPlanNodePlan
go
create table TP_RollingPlanNodePlan
(
PlanVersion varchar(10),
ProjectID int,
NodeID int,
PlanTime datetime
)
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 1, '2006-11-01')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 3, '2006-11-10')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 1, 4, '2006-11-20')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 1, '2006-11-20')
insert into TP_RollingPlanNodePlan(PlanVersion,ProjectID,NodeID,PlanTime) values('20060101', 2, 2, '2006-11-30')if object_id('pubs..test') is not null
drop table test
go--将结果放入一临时表
select a.ProjectID , a.NodeID, convert(varchar(10),a.PlanTime,120) as PlanTime , b.NodeName
into test
from TP_RollingPlanNodePlan a , TB_Node b
where a.NodeID = b.NodeID--用动态SQL实现结果
declare @sql varchar(8000)
set @sql = 'select ProjectID'
select @sql = @sql + ' , max(case NodeName when ''' + NodeName + ''' then PlanTime else null end) [' + NodeName + ']'
from (select distinct NodeName from test) as a
set @sql = @sql + ' from test group by ProjectID'
exec(@sql)
drop table TB_Node
drop table TP_RollingPlanNodePlan
drop table testProjectID 1、开工 2、铺底 3、下水 4、交工
----------- ---------- ---------- ---------- ----------
1 2006-11-01 NULL 2006-11-10 2006-11-20
2 2006-11-20 2006-11-30 NULL NULL