我的CTE如下
WITH details
AS ( SELECT FldId,Rev,Words,row_number() OVER ( PARTITION BY FldId ORDER BY Rev DESC ) AS rn
FROM WorkItemLongTexts
WHERE ID = 2855
)
SELECT f.ReferenceName,d.FldId,Words
FROM details AS d
INNER JOIN Fields AS f ON f.FldId = d.FldId
WHERE d.rn = 1 ;
以上返回以下输出
ReferenceName | FldId | Rev | Words Description 52 2 Description here Objectives 10257 2 Objectives here Specification 10258 6 Specification here Requirements 10259 6 Requirements here
我想应用PIVOT(或任何最佳选项),以便我可以获得如下输出
Description | Objectives | Specification | Requirements
这里的目标这里的目标规范这里的要求
PLS.建议.
谢谢
解决方法
WITH details
AS ( SELECT FldId,row_number() OVER ( PARTITION BY FldId ORDER BY Rev DESC ) AS rn
FROM WorkItemLongTexts
WHERE ID = 2855
),cte_1
AS ( SELECT f.ReferenceName,Words
FROM details AS d
INNER JOIN Fields AS f ON f.FldId = d.FldId
WHERE d.rn = 1
)
SELECT max(case [ReferenceName] WHEN 'Descripton' THEN [Words] ELSE NULL END) AS [Descripton],max(case [ReferenceName] WHEN 'Objectives' THEN [Words] ELSE NULL END) AS [Objectives],max(case [ReferenceName] WHEN 'Specification' THEN [Words] ELSE NULL END) AS [Specification],max(case [ReferenceName] WHEN 'Requirements' THEN [Words] ELSE NULL END) AS [Requirements]
FROM cte_1 ;
要么：
-- cte here as above SELECT Description,Objectives,Specification,Requirements FROM cte_1 PIVOT ( max(Words) FOR ReferenceName IN ( Description,Requirements ) ) AS PivotTable
