I'm not sure if you still need it, but here's a way to make it dynamic. I changed the table variable into a permanent table because I didn't want to set up everything to use a table valued parameter.
DECLARE @SQL nvarchar(MAX);
WITH ItemsMax AS(
SELECT TOP 1 COUNT(*) items --We define the maximum number of columns
from OrderItems
GROUP BY OrderID
ORDER BY items DESC
)
SELECT @SQL = --This is the header part. It's static and copied directly from the original cross tab query.
'WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ItemID) rowID,
OrderID,
ItemNumber
FROM OrderItems
)
SELECT OrderID'
+ ( --This is how we create the variable number of columns. The row numbers come from a Tally table.
--The concatenation method is explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT ' ,MAX(CASE WHEN rowID = ' + CAST(n AS varchar(7)) + ' THEN ItemNumber END) Item' + CAST(n AS varchar(7)) + CHAR(10)
FROM Tally t
JOIN ItemsMax im ON t.n <= im.items
ORDER BY t.n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
+ --This is the footer. It could contain additional columns added after the variable columns and it should include the FROM and GROUP BY clauses.
'FROM CTE
GROUP BY OrderID;';
EXEC sp_executesql @SQL;