It's not a brilliant script with the errors as they are: if you were to correct the ORDER BY (or better still, introduce a PK) and remove the ISNULLs from the output, it would make more sense to folks stumbling across it.
Pivot query:
SELECT
RowID1,
RowID2,
RowID3,
RowID4
FROM (
SELECT RowID = 'RowID'+convert(nvarchar(1),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), TEXT
FROM COMMENT_TEXT
) AS SourceTable
PIVOT (MAX(TEXT) FOR RowID IN (RowID1, RowID2, RowID3, RowID4)) AS PivotTable
Equivalent crosstab query:
SELECT
RowID1 = MAX(CASE WHEN RowID = 'RowID1' THEN TEXT ELSE NULL END),
RowID2 = MAX(CASE WHEN RowID = 'RowID2' THEN TEXT ELSE NULL END),
RowID3 = MAX(CASE WHEN RowID = 'RowID3' THEN TEXT ELSE NULL END),
RowID4 = MAX(CASE WHEN RowID = 'RowID4' THEN TEXT ELSE NULL END)
FROM (
SELECT RowID = 'RowID'+convert(nvarchar(1),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), TEXT
FROM COMMENT_TEXT
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden