fixed a syntax issue, and create the DDL for the CTE;

i got far enough to duplicate the one row issue before i ran out of gas...building the CTE took the wind out of my sails.

With CTE ([RowID],[Region], [Factor])

AS

(

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 [Factor], [Region]

FROM CTE

WHERE RowID <= 3)

AS TheSource

PIVOT

(

MAX([Factor]) FOR [Region] IN ([Capitol], [Central], [North])

)AS pvt