SQL Server 2005如何透视或跨标签数据来获得我的结果集?
我正在寻找生成一个存储过程,将采取projectIds的CSV字符串并返回以下结果集。SQL Server 2005如何透视或跨标签数据来获得我的结果集?
的专案编号和名称字段来自工作表中,而其余字段是里程碑的枢轴为每个项目的日期。 (37,39,41,45,47,50,53,72,73,75,77)中,里程碑字段是CID的某些里程碑,我希望每个相应的里程碑都显示为excel文件中的名称(I猜对使用别名为每个每个里程碑名称转换什么,我想让它显示为)
另外要注意的第一个里程碑场是BRS(CID = 37)的开始日期和里程碑其余字段是所有结束日期,包括里程碑cid 37和上面提到的其余部分。
的日期应该代表那里获得的数据有projectMilestone日期,如果有特定CID没有projectMilestone然后我需要使用ReleaseSchedule日期。我要去COALESCE(projectmilestone日期,发布日期)来完成这个任务。
里程碑名的字段标题是这样的:如果
CID NAME in result set (as field headers)
37 BRS
39 SRS
41 SAD
45 Product Profile Review
47 SE Integration
50 IDE
53 UAT
72 PE Testing
73 Code Freeze
75 Dark Pod
77 Production
@beth我有这方面的工作(那种)现在个人projecttId与下面的代码。但不是使用主键我有手动的case语句来结束数据的枢轴转换:
ALTER PROCEDURE [dbo].[rpt_ReportingMilestones]
(@ProjectId int = null)
AS
BEGIN
SELECT sco.CID,
sco.CodeName,
t1.EndDate as rsEndDate,
t2.EndDate as pmEndDate,
t2.CodeName as RAGStatus
INTO #TTT
FROM StatusCode sco
LEFT OUTER JOIN
(SELECT sc.CID,
rs.EndDate
FROM StatusCode sc
INNER JOIN ReleaseSchedule rs
ON sc.CID = rs.MilestoneCID
INNER JOIN Project p
ON rs.ReleaseID = p.ReleaseID
AND p.ProjectId = @ProjectId) as t1
ON sco.CID = t1.CID
LEFT OUTER JOIN
(SELECT sc.CID,
pm.EndDate,
sc2.CodeName
FROM StatusCode sc
INNER JOIN ProjectMilestone pm
ON sc.CID = pm.MilestoneCID
AND pm.ProjectID = @ProjectId
INNER JOIN StatusCode sc2
ON pm.RAGStatusCID = sc2.CID) as t2
ON sco.CID = t2.CID
WHERE sco.CID in (37, 39, 41, 45, 47, 50, 53, 72, 73, 75, 77)
CREATE TABLE #UUU
(rowid integer not null,
rowHeader nvarchar(50),
milestone1 DateTime,
ragstatus1 nvarchar(50),
milestone2 DateTime,
ragstatus2 nvarchar(50),
milestone3 DateTime,
ragstatus3 nvarchar(50),
milestone4 DateTime,
ragstatus4 nvarchar(50),
milestone5 DateTime,
ragstatus5 nvarchar(50),
milestone6 DateTime,
ragstatus6 nvarchar(50),
milestone7 DateTime,
ragstatus7 nvarchar(50),
milestone8 DateTime,
ragstatus8 nvarchar(50),
milestone9 DateTime,
ragstatus9 nvarchar(50),
milestone10 DateTime,
ragstatus10 nvarchar(50),
milestone11 DateTime,
ragstatus11 nvarchar(50))
INSERT INTO #UUU
SELECT 2 as RowId,
'Baseline' as rowHeader,
CASE WHEN CID = 37 THEN rsEndDate ELSE null END as Milestone1,
null,
CASE WHEN CID = 39 THEN rsEndDate ELSE null END as Milestone2,
null,
CASE WHEN CID = 41 THEN rsEndDate ELSE null END as Milestone3,
null,
CASE WHEN CID = 45 THEN rsEndDate ELSE null END as Milestone4,
null,
CASE WHEN CID = 47 THEN rsEndDate ELSE null END as Milestone5,
null,
CASE WHEN CID = 50 THEN rsEndDate ELSE null END as Milestone6,
null,
CASE WHEN CID = 53 THEN rsEndDate ELSE null END as Milestone7,
null,
CASE WHEN CID = 72 THEN rsEndDate ELSE null END as Milestone8,
null,
CASE WHEN CID = 73 THEN rsEndDate ELSE null END as Milestone9,
null,
CASE WHEN CID = 75 THEN rsEndDate ELSE null END as Milestone10,
null,
CASE WHEN CID = 77 THEN rsEndDate ELSE null END as Milestone11,
null
FROM #TTT
INSERT INTO #UUU
SELECT 3 as RowId,
'Adjusted',
CASE WHEN CID = 37 THEN pmEndDate ELSE null END as Milestone1,
CASE WHEN CID = 37 THEN RAGStatus ELSE null END as RAGStatus1,
CASE WHEN CID = 39 THEN pmEndDate ELSE null END as Milestone2,
CASE WHEN CID = 39 THEN RAGStatus ELSE null END as RAGStatus2,
CASE WHEN CID = 41 THEN pmEndDate ELSE null END as Milestone3,
CASE WHEN CID = 41 THEN RAGStatus ELSE null END as RAGStatus3,
CASE WHEN CID = 45 THEN pmEndDate ELSE null END as Milestone4,
CASE WHEN CID = 45 THEN RAGStatus ELSE null END as RAGStatus4,
CASE WHEN CID = 47 THEN pmEndDate ELSE null END as Milestone5,
CASE WHEN CID = 47 THEN RAGStatus ELSE null END as RAGStatus5,
CASE WHEN CID = 50 THEN pmEndDate ELSE null END as Milestone6,
CASE WHEN CID = 50 THEN RAGStatus ELSE null END as RAGStatus6,
CASE WHEN CID = 53 THEN pmEndDate ELSE null END as Milestone7,
CASE WHEN CID = 53 THEN RAGStatus ELSE null END as RAGStatus7,
CASE WHEN CID = 72 THEN pmEndDate ELSE null END as Milestone8,
CASE WHEN CID = 72 THEN RAGStatus ELSE null END as RAGStatus8,
CASE WHEN CID = 73 THEN pmEndDate ELSE null END as Milestone9,
CASE WHEN CID = 73 THEN RAGStatus ELSE null END as RAGStatus9,
CASE WHEN CID = 75 THEN pmEndDate ELSE null END as Milestone10,
CASE WHEN CID = 75 THEN RAGStatus ELSE null END as RAGStatus10,
CASE WHEN CID = 77 THEN pmEndDate ELSE null END as Milestone11,
CASE WHEN CID = 77 THEN RAGStatus ELSE null END as RAGStatus11
FROM #TTT
SELECT Rowid,
rowHeader,
MAX(Milestone1) AS 'BRS',
MAX(RagStatus1) AS 'BRS RAG',
MAX(Milestone2) AS 'SRS',
MAX(RagStatus2) AS 'SRS RAG',
MAX(Milestone3) AS 'SAD',
MAX(RagStatus3) AS 'SAD RAG',
MAX(Milestone4) AS 'Product Profile Review',
MAX(RagStatus4) AS 'Product Profile Review RAG',
MAX(Milestone5) AS 'SE Integration',
MAX(RagStatus5) AS 'SE Integration RAG',
MAX(Milestone6) AS 'IDE',
MAX(RagStatus6) AS 'IDE RAG',
MAX(Milestone7) AS 'UAT',
MAX(RagStatus7) AS 'UAT RAG',
MAX(Milestone8) AS 'PE Testing',
MAX(RagStatus8) AS 'PE Testing RAG',
MAX(Milestone9) AS 'Code Freeze',
MAX(RagStatus9) AS 'Code Freeze RAG',
MAX(Milestone10) AS 'Dark Pod',
MAX(RagStatus10) AS 'Dark Pod RAG',
MAX(Milestone11) AS 'Production',
MAX(RagStatus11) AS 'Production RAG'
FROM #UUU
GROUP BY Rowid, rowHeader
ORDER BY RowId
END
不知道你可以完全自动化你想要的一切。
重:我希望每个相应的里程碑显示为Excel文件名(我猜使用别名为每个每个里程碑名称转换什么,我希望它作为显示)
你可以使用查找表吗?
重:另外要注意的第一个里程碑场是BRS(CID = 37)的开始日期和全部结束日期
枢轴之前的里程碑其余字段,这些字段将必须在一个等效的输出列。在数据透视期间,您必须将聚合函数应用于值,如first(),min()或max()。
re:日期应该代表可用数据所在的projectMilestone日期,如果没有特定CID的projectMilestone,那么我需要使用ReleaseSchedule日期。
再次,您需要在数据透视之前完成此项工作。
您可以通过projectMilestone和releaseSchedule(仅适用于projectMilestone中的间隙)并查找要显示的列标题名称来获取未转换的结果集吗?
什么是项目里程碑日期表的结构? – Thomas 2011-01-20 18:03:38