Hi,
It's because you've not selected the RowID... See below.
DECLARE @cte TABLE
(
RowID INT,
Region VARCHAR(50),
Factor VARCHAR(50)
)
INSERT INTO @cte
SELECT 1, 'Capitol', 'Text1'
UNION ALL SELECT 2,'Capitol', 'Text2'
UNION ALL SELECT 3,'Capitol', 'Text3'
UNION ALL SELECT 1,'Central', 'Text4'
UNION ALL SELECT 2,'Central', 'Text5'
UNION ALL SELECT 3,'Central', 'Text6'
UNION ALL SELECT 1,'North','Text7'
UNION ALL SELECT 2,'North' ,'Text8'
UNION ALL SELECT 3,'North' ,'Text9'
SELECT
*
FROM
(
SELECT [RowID], [Factor], [Region]
FROM @cte
WHERE RowID <= 3
) as X PIVOT
(
MAX([Factor]) FOR [Region] IN ([Capitol], [Central], [North])
)AS pvt