公共表表达式的 PIVOT [英] PIVOT on Common Table Expression

问题描述

我的 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
         ,Rev
         ,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(或任何最好的选择)，以便我可以得到如下输出

I want to apply PIVOT (or whatever is the best option) so that i can get output as follows

Description         |     Objectives     |   Specification      |  Requirements  

此处描述     此处目标此处的规范      这里的要求

Description here        Objectives here         Specification here         Requirements here

请.建议.

谢谢

推荐答案

WITH  details
        AS ( SELECT FldId
                   ,Rev
                   ,Words
                   ,row_number() OVER ( PARTITION BY FldId ORDER BY Rev DESC ) AS rn
             FROM   WorkItemLongTexts
             WHERE  ID = 2855
           ),
      cte_1
        AS ( SELECT f.ReferenceName
                   ,d.FldId
                   ,Rev
                   ,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,
                                                          Objectives,
                                                          Specification,
                                                          Requirements ) ) AS PivotTable

这篇关于公共表表达式的 PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！