PIVOT on Common Table Expression

Question

I have a CTE as follows

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 ;

The above returns the following output

ReferenceName    |   FldId      |    Rev     |    Words
Description            52            2            Description here  
Objectives           10257           2            Objectives here  
Specification        10258           6            Specification here  
Requirements          10259           6            Requirements here

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

Pls. suggest.

Thanks

IMHO, Rob Farley's answer is the best, as it is the only one that works for me. It appears the syntax here is very delicate, almost anything can break it. — JosephDoggie, Mar 09 '21 at 19:23

Gabriel McAdams · Answer 1 · 2009-12-22T22:03:16.487

You do this:

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

Or you can add it to your CTE, like this:

;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

score 4 · Answer 2 · answered Dec 22 '09 at 21:50

4

Do something like:

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
;

answered Dec 22 '09 at 21:50

Rob Farley

15,625
5
44
58

1

I have tried many of these from various posts on the web. This is the ONLY one that worked for me. It looks like in selecting from unpivotted it picks up the pivoted fields also, so maybe that should be given a more generic name like 'OurQuery' ... still wonderful job!!! – JosephDoggie Mar 09 '21 at 15:54
1

@JosephDoggie The hassle is the way that pivot implies that any column not in the aggregate part of the pivot clause must be part of the group by. By planning the unpivotted version first (without any extra columns), you have a much cleaner result. – Rob Farley Mar 11 '21 at 07:19

Damir Sudarevic · Accepted Answer · 2009-12-22T22:19:57.650

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 ;

OR:

  -- cte here as above
  SELECT  Description
         ,Objectives
         ,Specification
         ,Requirements
  FROM    cte_1 PIVOT ( max(Words) FOR ReferenceName IN ( Description,
                                                          Objectives,
                                                          Specification,
                                                          Requirements ) ) AS PivotTable

getting error "Operand data type ntext is invalid for max operator." — stackoverflowuser, Dec 22 '09 at 22:13
`...THEN CAST([Words] AS nvarchar(max)`) instead of `THEN [Words]`; or nvarchar(4000) if 4000 is enough — Damir Sudarevic, Dec 22 '09 at 22:29

PIVOT on Common Table Expression

3 Answers3

Linked