ЦЕНТР по общему выражению таблицы

Question

ЦЕНТР по общему выражению таблицы

У меня есть 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

Я хочу применить ЦЕНТР (или независимо от того, что наилучший вариант) так, чтобы я мог быть произведен следующим образом

Description         |     Objectives     |   Specification      |  Requirements

Описание здесь Цели здесь Спецификация здесь Требования здесь

. предложить.

Спасибо

6

sql-server tsql

задан Damir Sudarevic 22 December 2009 в 22:42

3 ответа

Вы делаете это:

SELECT
    FldId,
    [Description],
    [Objectives],
    [Specification],
    [Requirements]
FROM (
    SELECT
        ReferenceName,
        FldId,
        REV,
        Words
    FROM CTE
    WHERE RowNumber = 1
) t
PIVOT (
    MIN(Words)
    FOR ReferenceName IN ([Description], [Objectives], [Specification], [Requirements])
) PIV

Или вы можете добавить его в свой CTE, например:

;WITH CTE2 AS (
    SELECT
        FldId,
        REV,
        [Description],
        [Objectives],
        [Specification],
        [Requirements],
        ROW_NUMBER() OVER (PARTITION BY FldId ORDER BY REV DESC) AS RowNumber
    FROM TBL
PIVOT (
        MIN(Words)
        FOR ReferenceName IN ([Description], [Objectives], [Specification], [Requirements])
    ) PIV
)

SELECT
    FldId,
    REV,
    [Description],
    [Objectives],
    [Specification],
    [Requirements]
FROM CTE2
WHERE RowNumber = 1

6

ответ дан 9 December 2019 в 20:44

Сделайте что-нибудь вроде:

with details as (...)
, unpivotted as (select f.ReferenceName, Words 
  from details as d
  inner join Fields as f
  on f.FldId=d.FldId
  where d.rn =1)
Select *
from unpivotted 
pivot 
(max(Words) for Description in ([Objectives],[Specification],[Requirements]) p
;

2

ответ дан 9 December 2019 в 20:44

Другие вопросы по тегам:

sql-server tsql

Похожие вопросы:

score 4 · Accepted Answer

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