sql-server – 公用表表达式上的PIVOT

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!