Your code seemed to work ok for me, the only thing I noticed was you missed the close parenthesis on the create table statement:
IF OBJECT_ID(N'Tempdb..#ProductTest', N'U') IS NOT NULL
DROP TABLE #ProductTest
GO
CREATE TABLE #ProductTest
(
Co_idxINT NOT NULL,
co_shortnameVARCHAR(100) COLLATE Latin1_General_CI_AS NULL,
prod_shortVARCHAR(100) COLLATE Latin1_General_CI_AS NULL,
int_minDECIMAL(4, 2) NULL,
int_rollDECIMAL(4, 2) NULL,
solicit_feeMONEY NULL
);
INSERT INTO #ProductTest(Co_Idx, co_shortname, prod_short, int_min, int_roll, solicit_fee)
SELECT 1,'Test1','Product1',8.75, 9.76, 100 UNION ALL
SELECT 1,'Test1','Product2',8.75, 9.76, 100 UNION ALL
SELECT 1,'Test1','Product3',8.75, 9.76, 100 UNION ALL
SELECT 2,'Test2','Product1',6.75, 8.76, 200 UNION ALL
SELECT 3,'Test3','Product1',4.75, 6.76, 250;
SELECT
co_idx,Heading,
[1] AS value1,
[2] AS value2,
[3] AS value3
FROM
(
SELECT co_idx, Heading, RowNum, [Value]
FROM
(
SELECT
CAST (Co_idx AS VARCHAR(200)) AS co_idx,
CAST (ISNULL(Co_ShortName,'') AS VARCHAR(200)) AS [Company],
CAST (ISNULL(Prod_Short,'') AS VARCHAR(200)) AS [Product Name],
CAST (ISNULL(int_min,'0') AS VARCHAR(200)) AS [Initial Interest Rate],
CAST (ISNULL(int_roll,'0') AS VARCHAR(200)) AS [Roll Rate],
CAST (' ' AS VARCHAR(200)) AS [ ],
ROW_NUMBER() OVER (PARTITION BY co_shortname ORDER BY co_shortname) AS RowNum
FROM #ProductTest
) AS p
UNPIVOT
(
[Value] FOR Heading IN
([Company],[Product Name],[Initial Interest Rate],[Roll Rate],[ ])
)AS UP
) AS UP1
PIVOT
(
Max ([Value]) FOR RowNum in ([1],[2],[3])
) AS P1
ORDER BY co_idx,
CASE Heading
WHEN 'Company' THEN 1
WHEN 'Product Name' THEN 2
WHEN 'Initial Interest Rate' THEN 3
WHEN 'Roll Rate' THEN 4
Else 5
END
Result:
1CompanyTest1Test1Test1
1Product NameProduct1Product2Product3
1Initial Interest Rate8.758.758.75
1Roll Rate9.769.769.76
1
2CompanyTest2NULLNULL
2Product NameProduct1NULLNULL
2Initial Interest Rate6.75NULLNULL
2Roll Rate8.76NULLNULL
2 NULLNULL
3CompanyTest3NULLNULL
3Product NameProduct1NULLNULL
3Initial Interest Rate4.75NULLNULL
3Roll Rate6.76NULLNULL
3 NULLNULL