This seems to give the same results as your query. The problem is that you need a single row number, instead of having to calculate a different row number for each column.

Using a cross tab, you'll improve performance as the table will be read once.

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER( PARTITION BY SampleNo ORDER BY Complete_Date) rn

FROM #mytable

)

SELECT SampleNo,

MAX( CASE WHEN rn = 1 THEN TestType1a END) Test_Type1,

MAX( CASE WHEN rn = 1 THEN Complete_Date END) Complete_Date1,

PAProjid,

MAX( CASE WHEN rn = 2 THEN TestType1a END) Test_Type2,

MAX( CASE WHEN rn = 2 THEN Complete_Date END) Complete_Date2,

MAX( CASE WHEN rn = 3 THEN TestType1a END) Test_Type3,

MAX( CASE WHEN rn = 3 THEN Complete_Date END) Complete_Date3,

MAX( CASE WHEN rn = 4 THEN TestType1a END) Test_Type4,

MAX( CASE WHEN rn = 4 THEN Complete_Date END) Complete_Date4,

MAX( CASE WHEN rn = 5 THEN TestType1a END) Test_Type5,

MAX( CASE WHEN rn = 5 THEN Complete_Date END) Complete_Date5,

MAX( CASE WHEN rn = 6 THEN TestType1a END) Test_Type6,

MAX( CASE WHEN rn = 6 THEN Complete_Date END) Complete_Date6,

MAX( CASE WHEN rn = 7 THEN TestType1a END) Test_Type7,

MAX( CASE WHEN rn = 7 THEN Complete_Date END) Complete_Date7,

MAX( CASE WHEN rn = 8 THEN TestType1a END) Test_Type8,

MAX( CASE WHEN rn = 8 THEN Complete_Date END) Complete_Date8

FROM CTE

GROUP BY SampleNo, PAProjid

ORDER BY SampleNo

You need to complete the query as you missed 2 columns (DateAndTime & Comments1a) and I won't write the 26 column groups.

Read the following article about CROSS TABS http://www.sqlservercentral.com/articles/T-SQL/63681/

and ask any questions that you might have.