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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With